Forum Discussion
group values based on a condition
Hi guys,
I have the following dataset:
| DATE | ID | category | Status | ||
| 01/02/2021 | 001 | aa | OK | ||
| 02/02/2021 | 001 | bb | OK | ||
| 02/02/2021 | 001 | cc | OK | ||
| 03/02/2021 | 002 | aa | OK | ||
| 04/02/2021 | 002 | bb | KO | ||
| 09/02/2021 | 002 | cc | OK | ||
| 10/02/2021 | 003 | aa | OK | ||
| 11/02/2021 | 003 | bb | OK | ||
| 12/02/2021 | 003 | cc | OK |
I want to create a matrix that counts my IDs but Distinctly, and I want to display "KO" if at least one value of the status is KO
in the exemple shown above, it would be like this:
001 | OK
002 | KO
003 | OK
My final goal is to create a matrix in which I can count my OK and KO following the above logic.
thanks!
Try using the equivalent measure as a filter in the filter pane for the visual setting the value to 1:
Filtered Status = VAR StatusValues = VALUES ( 'Table'[ID] ) VAR KOValues = CALCULATETABLE ( VALUES ( 'Table'[ID] ), 'Table'[Status] = "KO" ) VAR OKTable = ADDCOLUMNS ( EXCEPT ( StatusValues, KOValues ), "Stat", "OK" ) VAR KOTable = ADDCOLUMNS ( KOValues, "Stat", "KO" ) VAR FiltTable = UNION ( OKTable, KOTable ) RETURN COUNTROWS ( INTERSECT ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Status] ), FiltTable ) )I've attached the sample PBIX file
4 Replies
Try using the equivalent measure as a filter in the filter pane for the visual setting the value to 1:
Filtered Status = VAR StatusValues = VALUES ( 'Table'[ID] ) VAR KOValues = CALCULATETABLE ( VALUES ( 'Table'[ID] ), 'Table'[Status] = "KO" ) VAR OKTable = ADDCOLUMNS ( EXCEPT ( StatusValues, KOValues ), "Stat", "OK" ) VAR KOTable = ADDCOLUMNS ( KOValues, "Stat", "KO" ) VAR FiltTable = UNION ( OKTable, KOTable ) RETURN COUNTROWS ( INTERSECT ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Status] ), FiltTable ) )I've attached the sample PBIX file
Anonymous , Try a measure like
maxx(summarize( Table, Table[ID], "_1", max(Table[Status]), "_2", calculate(Max(Table[Status]), filter(Table, Table[Status] ="KO"))) , if(not(Isblank(_2)), _2,_1))
You can add a calculated column that applies the rule at the ID granularity.
StatusID = VAR AllStatus = CALCULATE ( VALUES ( Table1[Status] ), ALLEXCEPT ( Table1, Table1[ID] ) ) RETURN IF ( "KO" IN AllStatus, "KO", "OK" )I can't quite tell what you're trying to count but this column might make it easier.
