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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors