Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've a table as below which contains the CaseID of a case, the division of a case and the predicted division of a case. This predicted value is computed by calling a ML model.
CaseID | Division | Division Prediction |
12345 | A | A |
12346 | A | B |
12347 | B | B |
12348 | B | C |
12349 | C | C |
12350 | C | C |
12351 | C | A |
I would like to compute a table to summarise the results. I've tried to use ADDCOLUMN but can't get it right. Anyone, pls help to provide the correct dax syntax. Thanks! 🙂
Division | No Cases | No Predictions |
A | 2 | 2 |
B | 2 | 2 |
C | 3 | 3 |
Solved! Go to Solution.
Thank you for all help! I've managed to find the solution.
ADDCOLUMNS(
values(Table[Division]),
"No Cases", CALCULATE(countrows(Table)),
"No Predictions", CALCULATE(COUNTROWS(Table), Table[Division Prediction]=earlier([Division]), All(Table[Division]))
)
I have used the first column in AddColumns as the table I intend to loop through, which is Division.
Then use Calculate to do a context transition, bringing Table[Division] the row context into the filter context. Hence, "No Cases" will have the correct figure.
Then use Earlier to reference the filter context from outside of Calculate, to use it on Table[Division Prediction]. Remove the filter on Table[Division]
Thank you for all help! I've managed to find the solution.
ADDCOLUMNS(
values(Table[Division]),
"No Cases", CALCULATE(countrows(Table)),
"No Predictions", CALCULATE(COUNTROWS(Table), Table[Division Prediction]=earlier([Division]), All(Table[Division]))
)
I have used the first column in AddColumns as the table I intend to loop through, which is Division.
Then use Calculate to do a context transition, bringing Table[Division] the row context into the filter context. Hence, "No Cases" will have the correct figure.
Then use Earlier to reference the filter context from outside of Calculate, to use it on Table[Division Prediction]. Remove the filter on Table[Division]
divpredic =
CALCULATE(COUNTROWS('Tablename'),DISTINCT(Division),DISTINCT(Prediction))
Thanks Devanshi! But the measure didn't give the right output 🙂
I would create a dimension table for your divisions, simply DISTINCT('Table'[Division]), create an active relationship from this to the division column in your fact table and create an inactive relationship from it to your predicted division column. You could then create measures like
No Cases = COUNTROWS( 'Table' )
No Predicted Cases = CALCULATE( COUNTROWS('Table'), USERELATIONSHIP( 'Table'[Predicted Division], Division[Division]) )
Hi @meiwah
please try
NeawTable =
SUMMARIZE (
'Table',
'Table'[Division],
"No Cases", COUNTROWS ( 'Table' ),
"No Predictions", DISTINCTCOUNT ( 'Table'[Division Prediction] )
)
Thanks Tamerj1! But the measure didn't give the right output 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |