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
Anonymous
Not applicable

Countx with some conditions

Hello!!

Im trying to do this in a calculated column in dax, im not sure where is the mistake.

 

I have a table with information like this:17.JPG

 

I want to count by country, supplier and mat_group; im trying with this dax expresion, for test the supplier and the mat_group, but I dont have the result that I expect.

 

Count = COUNTX(FILTER('Mytable','Mytable'[SUPPLIER]=EARLIER('Mytable'[SUPPLIER]) && 'Mytable'[MAT_GROUP]=EARLIER('Mytable'[MAT_GROUP])),'Mytable'[MAT_GROUP])

 

I actually received the count of the actual count, but I expected the column expect count.

 

I hope someone could help me!

 

Greetings

Mónica

 

 

 

 

 

1 ACCEPTED SOLUTION

@Anonymous Please try below using "New Column"

 

ActualCount = CALCULATE(COUNTROWS(MatGroups),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))
ExpectCount = CALCULATE(DISTINCTCOUNT(MatGroups[MatGroup]),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))

image.png

 

Note - You can solve the same in "Power Query" as well using "GROUP BY" option..

 

Hope this helps !!





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

6 REPLIES 6
MarkLaf
Solution Sage
Solution Sage

This is a pretty simple calculated column, especially if you only have COUNTRY, SUPPLIER, and MAT_GROUP columns:

Count = CALCULATE(COUNTROWS(Mytable))

If you do have other columns, then use this formula:

Count = CALCULATE(COUNTROWS(Mytable),ALLEXCEPT(Mytable,Mytable[COUNTRY],Mytable[SUPPLIER],Mytable[MAT_GROUP])) 

 

I'll also note that you can get this in the report layer with a few drag and drops - click the table visual, drag in all columns, and then drag in a duplicate column and set to count:

 

image.png

Anonymous
Not applicable

@MarkLaf thanks for your reply; 

 

Maybe I dont can explain me, Im looking when I filter country and supplier show me the count of diferentes mat_groups, for example

Country   Supplier       Mat_group

US               A                  123

US               A                  123

US               A                  456

US               B                   123

 

When I filter US and Supplier A, I want to see in count column 2 (the two diferent mat_group) and when y filter supplier B i want to see count 1 (mat_group 123)

 

I have more columns in the table, almost 150 

 

I dont use the solution in the report layer, because with the information im going to do another calculations before show in the report layer.

 

Thanks again

Monica

@Anonymous Please create a "New Measure" as below:

 

CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])

image.pngimage.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

@PattemManohar thanks a lot for you reply.

 

Is possible to do this in a column? I want to have the value in a column because I need for another calcutations.

 

I tried this solution in a column but it doesnt work that I want.

 

Thanks Again

 

Monica

@Anonymous Please try below using "New Column"

 

ActualCount = CALCULATE(COUNTROWS(MatGroups),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))
ExpectCount = CALCULATE(DISTINCTCOUNT(MatGroups[MatGroup]),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))

image.png

 

Note - You can solve the same in "Power Query" as well using "GROUP BY" option..

 

Hope this helps !!





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

@PattemManohar thanks a lot for your help.

 

It works perfect for me.

 

Thanks again

Monica

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.