Forum Discussion
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:
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
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]))
Note - You can solve the same in "Power Query" as well using "GROUP BY" option..
Hope this helps !!
6 Replies
- MarkLaf
Super User
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:
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
- PattemManohar
Community Champion
Anonymous Please create a "New Measure" as below:
CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])
