[SOLVED] DAX TOPN Ascending Returns Blank Values

DAX TOPN Ascending Returns Blank Values

In Power Pivot, I have the following DAX Measure which works great including being connected to a slicer using CUBEVALUE.

Meas_TopPerformerWeighted:=VAR TopAgentName =
    CALCULATE (
        FIRSTNONBLANK ([Agent Name], 1 ),
        TOPN (
            1,
            ALL ([Agent Name] ),
            [Meas_OverallScoreWeighted], DESC
        )
    )
VAR TopScore =
    CALCULATE (
        [Meas_OverallScoreWeighted],
        [Agent Name] = TopAgentName
    )
RETURN
    TopAgentName & ": " & TopScore

But if I change the order to ASC to find the low score it works for the overall, but not when I utilize the slicer in the CUBEVALUE function. It returns blank for any date value chosen. And there is data on every row in the data model. No blank values. Any assistance here would be much appreciated.

I was expecting it to perform the calculation and then return the bottom result from that result. It will do it with all values selected, but not when paired with the date slicer usig CUBEVALUE.

Solution

The issue is likely that there are empty member intersections being returned as the bottom value. We can eliminate those by summarizing the table by agent first. I replaced the firstnonblank() with SELECTEDVALUE(), which will return blank or a custom expression when there is a tie. This is just preference though.

Meas_TopPerformerWeighted:=
VAR TopAgentName =
    CALCULATE (
        SELECTEDVALUE ( [AGENT NAME] ),
        TOPN (
            1,
            SUMMARIZE (
                table,
                [agent Name],
                "Meas_OverallScoreWeighted", [Meas_OverallScoreWeighted]
            ),
            [Meas_OverallScoreWeighted], ASC
        )
    )
VAR TopScore =
    CALCULATE ( [Meas_OverallScoreWeighted], [Agent Name] = TopAgentName )
RETURN
    TopAgentName & ": " & TopScore