Hi,
I have created two measure which I thought would work the same, (DEF; table name);
< CALCULATE(SUMX(DEF,DEF[Unit]*DEF[Value]),DEF[Area]="A")>
< SUMX(FILTER(DEF,DEF[Area]="A"),DEF[Unit]*DEF[Value])>
They both give the same result as a measure but when dropped into the pivot table the CALCULATE sometimes spills down, I seems to be dependent
on the order entered. Is there a reason for this or is just 'one of those things' , not a major problem just curious.
Richard.
Filter context is the keyword.
Filter https://www.sqlbi.com/articles/row-c...ontext-in-dax/
=CALCULATE(SUM([Unit x Value]),DEF[Area]="A")
CALCULATE force filter to get only Area="A" on every row.
Filter context doesn't apply to this Measure as Calculate filter overwrite filter to Area="A"
=SUMX(FILTER(DEF,DEF[Area]="A"),DEF[Unit x Value])
FILTER(DEF,DEF[Area]="A") -> Filter Area="A" to table, Table only has Area A data
Area Value Unit A 34 10 A 34 9
Filter context on each row filter Table that only has "A" for Area
For Area than is not "A", Table doesn't have any data, SUM of null data show null
Hi Bo ry
This is probably why I'm still more comfortable with calculated columns , I've read through the article,
I'm still a bit confused by row and filter context, I thought the CALCULATE would enforce row context and where the
the CALCULATE filer was not true there would be null value.
To add to this I've attached an workbook show that measure it can work without the spill down? column name calc. I've also put the >=< sumx (filter > along side for comparison.
Look forward to hearing form you.
RD
5# from https://dax.guide/calculate/
CALCULATE filter takes over Filter Context if there is any conflict.
=CALCULATE(SUM([Unit x Value]),DEF[Area]="A")
All filter context is chang to [Area]="A"
only 1 filter here [Area]="A"
=SUMX(FILTER(DEF,DEF[Area]="A"),DEF[Unit]*DEF[Value])
This is SUMX calculated on FILTER Table that only has [Area]="A".
Area Value Unit A 34 10 A 34 9
1st filter on table [Area]="A"
Then 2nd filter from Filter context A, B , C ,D
B, C , D doesn't has any data, value show as null
Hi there,
thanks for the help in explaining, I'll mark this as closed unless you have any further reading suggestions.
RD
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks