DAX Totals do not add up with COUNTROWS

[thedesk]

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)

))

[amitchandak]

@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

[thedesk · Accepted 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)