Query based Dynamic segmentation (NO lookup table in data model)

DAX DAX Calculations
1

Think this is a unique need. Need to write a DAX query that would do segmentation on-the-fly based on a DATATABLE created in query cannot create grouping/segmentation table in data model for my need.

Below is the model

image

Here is the query which does not work as it expects a base table. Is there a way we can do this without a base table defined in data model?

DEFINE
    VAR BedroomRange =
        SUMMARIZE (
            ADDCOLUMNS (
                'Property',
                "BedRoom Range", SWITCH (
                    TRUE (),
                    'Property'[Nbr. Of Bedrooms] >= 0
                        && 'Property'[Nbr. Of Bedrooms] <= 4, "0 - 4",
                    'Property'[Nbr. Of Bedrooms] >= 5
                        && 'Property'[Nbr. Of Bedrooms] <= 7, "5 - 7",
                    'Property'[Nbr. Of Bedrooms] >= 8
                        && 'Property'[Nbr. Of Bedrooms] <= 10, "8 - 10",
                    'Property'[Nbr. Of Bedrooms] >= 11
                        && 'Property'[Nbr. Of Bedrooms] <= 100, "11 - 100",
                    'Property'[Nbr. Of Bedrooms] >= 101, "> 100",
                    "No Bedrooms"
                )
            ),
            'Property'[Nbr. Of Bedrooms],
            [BedRoom Range]
        )
    MEASURE 'Property'[Arrears] =
        CALCULATE (
            [ArrearsAmt], // this is a measure defined separately 
            TREATAS (
                SUMMARIZE ( 'Property', 'Property'[Nbr. Of Bedrooms] ),
                BedroomRange[Nbr. Of Bedrooms]
            )
        )
EVALUATE
SUMMARIZE ( BedroomRange, [BedRoom Range], "lsev", [LSEV By Range] )
2

Really there is no way to do this without a base table to get the dynamic segmentation.

See the below though for some ideas.

This covers the options you have with this. It might not totally suit your requirements, but you might get an idea from it.

1 Like
3

Hi @dattanale, we’ve noticed that no response has been received from you since the 12th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!