Search code examples
powerbidax

Power BI - Using Summarize and Maxx, then remove filter

I'm working on a visual which is a number of guages, each related to a particular location within the organisation. I'm trying to create a measure which will give me the same max value across all locations. As the numbers can vary quite a lot I need this to be dynamic, rather than a set amount. I've defined this as the largest sum of values across the given time span, across all shops. When testing the query it's doing the calculations perfectly, I get a table of the summarized values for each location, but I can't seem to figure out how to return the max value for all (ignoring the filter on the visual). Depending on where I try to remove any filters, I either get the sum total of all locations put together, or only the sum total for that location alone.

This is my current query:

TargetGuageMax = 
    var __hourtbl = CALCULATETABLE(
    SUMMARIZE(
        Data
        , Data[Shop]
        , "MaxHours", CALCULATE(SUMX(Data,[Hours])
        )
    )
    , ALLEXCEPT(Data,Data[PeriodEnd])
    , FILTER(Data,Data[Shop]<>"LocationXX")
    , FILTER(Data,Data[Shop]<>"")
    , FILTER(Data,Data[Shop]<>"LocationYY")
    )

    var guagemax = CALCULATE(MAXX(__hourtbl,[MaxHours]))
    return guagemax

The last var is only there so I could try a few different solutions easily by changing the return var.

This is how the page currently looks. The table is calculating correctly, and the value in the card is what I'm looking for. I just need that to be the max value of all the guages. Any help would be appreciated.

I have also tried both of these at the end:

CALCULATE(MAXX(__hourtbl,[MaxHours]),ALLEXCEPT(Data,Data[PeriodEnd]))
&
CALCULATE(MAXX(__hourtbl,[MaxHours]),REMOVEFILTERS(Data[Shop])

Both yielding the same results.

Example of guages :

enter image description here

Solution

  • I eventually figured it out, posting it here in case anyone else runs into the same scenario. The key is order of operations. Slicers are applied before DAX, so I needed to move the ALLEXCEPT into the table input for the summarize function, and then filter out the unwanted elements in the SUMX.

      var __hourtbl = CALCULATETABLE(
SUMMARIZE(
    ALLEXCEPT(Data,Data[PeriodEnd])
    , Data[Shop]
    , "MaxHours", CALCULATE(SUMX(Data,[Hours])
        , FILTER(Data,Data[Shop]<>"LocationXX")
        , FILTER(Data,Data[Shop]<>"")
        , FILTER(Data,Data[Shop]<>"LocationYY")
    )
)
)

return CALCULATE(MAXX(__hourtbl,[MaxHours]))