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
Anonymous , I am assuming you need a rank tie breaker to overcome this
refer https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-Columns/ba-p/918655
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
Hi amitchandak ,
But what I see following this link is a simple ranking by kind of index column.
Sorry, looks like it doesn't apply to my example.