cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MonicaElizondo Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Countx with some conditions

@MonicaElizondo 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 Datanaut !





View solution in original post

6 REPLIES 6
MarkLaf Member
Member

Re: Countx with some conditions

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

MonicaElizondo Regular Visitor
Regular Visitor

Re: Countx with some conditions

@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

Super User
Super User

Re: Countx with some conditions

@MonicaElizondo 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 Datanaut !





MonicaElizondo Regular Visitor
Regular Visitor

Re: Countx with some conditions

@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

Super User
Super User

Re: Countx with some conditions

@MonicaElizondo 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 Datanaut !





View solution in original post

Highlighted
MonicaElizondo Regular Visitor
Regular Visitor

Re: Countx with some conditions

@PattemManohar thanks a lot for your help.

 

It works perfect for me.

 

Thanks again

Monica

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,202)