Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

TOP1 measure with double filter

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 ...
  • mahoneypat's avatar
    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