TOP1 measure with double filter

[Anonymous]

I'm stuck with a simple task and I need your help.

I have a simple dataset where I need to show top1 state by the number of alerts.

I can write a measure that calculates it correctly in case we have an obvious leader.

But when there are several states with the same number of alerts, I need to show the one that was recorder most recently.

Other than that I shouls show the number of alerts over this period of time.

Please help

https://drive.google.com/file/d/1M4Oe9REDZ0xHq7gFVh2KimNXw4DIqadh/view?usp=sharing 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mahoneypat · Accepted solution]

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]

It works, thank you! I would need several days to understand what it does inside but it makes the job.

[amitchandak]

@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/

[Anonymous]

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.