Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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]))
Note - You can solve the same in "Power Query" as well using "GROUP BY" option..
Hope this helps !!
Proud to be a PBI Community Champion
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
@Anonymous Please create a "New Measure" as below:
CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])
Proud to be a PBI Community Champion
@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]))
Note - You can solve the same in "Power Query" as well using "GROUP BY" option..
Hope this helps !!
Proud to be a PBI Community Champion
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |