Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a table (RISK_EVALUATION) that contains risk evaluations for RISKIDs (can be multiple) along with dates and outcomes. I then created a measure that I intent to include in a matrix to count how many risks have the latest evaluation as "Not Assessed".
The matrix will have the last day of the month from a calendar table as columns. A RISKID should be counted on that month if the latest evaluation, even if done many months ago, had a value of "Not Assessed". It'd look like this:
Sep-23 | Oct-23 | Nov-23 | Dec-23 | |
# Risks Not Assessed | 5 | 5 | 4 | 4 |
My current measure:
Solved! Go to Solution.
I have figure it out. My error was on the variable tmp1 which was producing a table with the latest date for all RISKIDs, rather than the latest for each RISKID.
I've replaced that variable by the following:
var tmp1 =
SUMMARIZE(
FILTER(
FILTER(RISK_EVALUATION,RISK_EVALUATION[RE_DATE]<=_selectdate),
RISK_EVALUATION[RE_DATE]
= CALCULATE (
MAX ( RISK_EVALUATION[RE_DATE] ),
ALLEXCEPT ( RISK_EVALUATION, RISK_EVALUATION[RISKID] )
)
),[RISKID],[RE_DATE])
This actually eliminates the need for the variable tmp as well.
I have figure it out. My error was on the variable tmp1 which was producing a table with the latest date for all RISKIDs, rather than the latest for each RISKID.
I've replaced that variable by the following:
var tmp1 =
SUMMARIZE(
FILTER(
FILTER(RISK_EVALUATION,RISK_EVALUATION[RE_DATE]<=_selectdate),
RISK_EVALUATION[RE_DATE]
= CALCULATE (
MAX ( RISK_EVALUATION[RE_DATE] ),
ALLEXCEPT ( RISK_EVALUATION, RISK_EVALUATION[RISKID] )
)
),[RISKID],[RE_DATE])
This actually eliminates the need for the variable tmp as well.
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |