Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I am struggling to get totals to add up whereby the cell results are correct.
My DAX is below ...
=CALCULATE(COUNTROWS(data),FILTER(data,data[Cost]<>MIN(d_Courses[1 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[2 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[3 courses £net])
&& data[Attend #]>0))
and have also tried variation of... with same results..
=COUNTROWS(CALCULATETABLE(data,
FILTER(data,data[Cost]<>MIN(d_Courses[1 course £net]) && data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[2 course £net]) && data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[3 courses £net]) && data[Attend #]>0)
))
Solved! Go to Solution.
Many thanks for your help.
I couldnt get the SUMMARIZE to work, however the video link you provided using the IF(HASONEVALUE did work, but only if I created extra measures and wouldnt work if I simply used the expressions that went into the measure.
I have found another way to make this work, swapping the MIN to RELATED. Not sure why this has made it work, but it has ???
CALCULATE(COUNTROWS(data),FILTER(data,data[Cost]<>MIN(d_Courses[1 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[2 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[3 courses £net])
&& data[Attend #]>0)
@thedesk , As this includes row level calculation. Based on the visual group by you need to create a measure like
Sumx(summarize(Table, Table[COl1], "_1",=CALCULATE(COUNTROWS(data),FILTER(data,data[Cost]<>MIN(d_Courses[1 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[2 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[3 courses £net])
&& data[Attend #]>0))),[_1])
Col1 is the group by/unsummarized column in visual
also check -https://www.youtube.com/watch?v=ufHOOLdi_jk
Many thanks for your help.
I couldnt get the SUMMARIZE to work, however the video link you provided using the IF(HASONEVALUE did work, but only if I created extra measures and wouldnt work if I simply used the expressions that went into the measure.
I have found another way to make this work, swapping the MIN to RELATED. Not sure why this has made it work, but it has ???
CALCULATE(COUNTROWS(data),FILTER(data,data[Cost]<>MIN(d_Courses[1 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[2 course £net])
&& data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[3 courses £net])
&& data[Attend #]>0)
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |