DAX Trickery

Rick_Gregory

Here's an example of the Dynamic Segmentation approach I was thinking of.

Here's a sample PBIX.

I used a pattern similar to one presented here:

https://www.sqlbi.com/articles/optimizing-duplicated-dax-expressions-using-variables/

Data model:

Then the Segmentation measures are:

Customers by Segment = 
SUMX (
    Segments,
    COUNTROWS (
        FILTER (
            VALUES ( Sales[Customer ID] ),
            VAR MoMPct = [Sales MoM %]
            RETURN
                NOT ( ISBLANK ( MoMPct ) )
                && MoMPct >= Segments[Lower]
                && MoMPct < Segments[Upper]
        )
    )
)

Sales by Segment = 
SUMX (
    Segments,
     SUMX ( 
         VALUES ( Sales[Customer ID] ),
         VAR MoMPct = [Sales MoM %]
         VAR SalesMeasure = [Sales]
         RETURN
             IF (
                 NOT ( ISBLANK ( MoMPct ) )
                 && MoMPct >= Segments[Lower]
                 && MoMPct < Segments[Upper],
                 SalesMeasure
             )
    )
)

Both measures assume Segments don't overlap, otherwise they would have to be written more like the versions on DAX Patterns.

Output of measures looks like:

Regards,

Owen