Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
very new to Power BI and just starting to use measures. What would be my formula if I want to show a visual that shows items with a fail result using their latest assessment date.
Department | Item | Assessment date | Assessment Result |
1 | a | 1/01/2021 | Pass |
1 | a | 1/01/2022 | Pass |
1 | b | 1/01/2021 | Fail |
1 | b | ||
1 | c | 1/01/2021 | Pass |
1 | c | 1/01/2022 | Fail |
2 | a | 1/01/2021 | Pass |
2 | a | 1/01/2022 | Pass |
2 | b | 1/01/2021 | Pass |
2 | b | 1/01/2022 | Pass |
2 | c | 1/01/2021 | Fail |
2 | c | 1/01/2022 | Fail |
3 | a | 1/01/2021 | Fail |
3 | a | 1/01/2022 | Pass |
3 | b | 1/01/2021 | Fail |
3 | b | 1/01/2022 | Fail |
3 | c | 1/01/2020 | Pass |
3 | c | 1/01/2021 | Fail |
An output using above which I wanted to show using bar graph as follows
Department | Count of Fails |
1 | 2 |
2 | 1 |
3 | 2 |
Thanks.
Solved! Go to Solution.
@arviaus
Use this measure:
Count =
VAR __T =
ADDCOLUMNS(
SUMMARIZE( Table03 , Table03[Department] , Table03[Item]),
"Status" ,
VAR __MaxDate = CALCULATE( MAX( Table03[Assessment date] ) )
VAR __Result = CALCULATE( MAX( Table03[Assessment Result] ) , Table03[Assessment date] = __MaxDate )
RETURN
INT( __Result = "Fail" )
)
VAR __Result =
SUMX( __T , [Status] )
RETURN
__Resul
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@arviaus
Please verify your expected output.
Example:
Dep 3 should give you 3
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
No, Dep 3 should be 2, because item a's latest assessment is 2022 and is a Pass.
@arviaus
Use this measure:
Count =
VAR __T =
ADDCOLUMNS(
SUMMARIZE( Table03 , Table03[Department] , Table03[Item]),
"Status" ,
VAR __MaxDate = CALCULATE( MAX( Table03[Assessment date] ) )
VAR __Result = CALCULATE( MAX( Table03[Assessment Result] ) , Table03[Assessment date] = __MaxDate )
RETURN
INT( __Result = "Fail" )
)
VAR __Result =
SUMX( __T , [Status] )
RETURN
__Resul
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @arviaus
Use dax code :
Fails = calculate( distinctcount('yourtable'[Item]), 'yourtable'[ Assessment]="Fail" )
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thanks for the reply; however, this does not take into account the latest date for each item.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |