Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thedesk
New Member

DAX Totals do not add up with COUNTROWS

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)

))

1 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)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors