Forum Discussion
TOP1 measure with double filter
- 5 years ago
Please try this expression. Note that your original measure was not correct as it did not have CALCULATE() around the COUNT. This expression makes a virtual table with the states, their counts, and a small # (<1) to differentiate based on most recently reported. The sum of these is used to get a single Top 1. Note you could have concatenated your Top1 so that if a tie occurs both would show.
Top1 New =
VAR summary =
ADDCOLUMNS (
ADDCOLUMNS (
DISTINCT ( Sheet1[State] ),
"cCount",
CALCULATE (
COUNT ( Sheet1[Alerts] )
),
"cDays",
1
/ (
2
* CALCULATE (
DATEDIFF (
MAX ( Sheet1[Date ] ),
TODAY (),
DAY
)
)
)
),
"cTotal",
[cDays] + [cCount]
)
VAR maxtotal =
MAXX (
summary,
[cTotal]
)
RETURN
MINX (
FILTER (
summary,
[cTotal] = maxtotal
),
Sheet1[State]
)Regards,
Pat
Please try this expression. Note that your original measure was not correct as it did not have CALCULATE() around the COUNT. This expression makes a virtual table with the states, their counts, and a small # (<1) to differentiate based on most recently reported. The sum of these is used to get a single Top 1. Note you could have concatenated your Top1 so that if a tie occurs both would show.
Top1 New =
VAR summary =
ADDCOLUMNS (
ADDCOLUMNS (
DISTINCT ( Sheet1[State] ),
"cCount",
CALCULATE (
COUNT ( Sheet1[Alerts] )
),
"cDays",
1
/ (
2
* CALCULATE (
DATEDIFF (
MAX ( Sheet1[Date ] ),
TODAY (),
DAY
)
)
)
),
"cTotal",
[cDays] + [cCount]
)
VAR maxtotal =
MAXX (
summary,
[cTotal]
)
RETURN
MINX (
FILTER (
summary,
[cTotal] = maxtotal
),
Sheet1[State]
)
Regards,
Pat
- Anonymous5 years agoNot applicable
It works, thank you! I would need several days to understand what it does inside but it makes the job.