New Enterprise DNA Initiatives

Grouping via Calculated Column issue

HI,

Im following Sam`s example in the video in DAX Formula Pattern training on Grouping via calculated columns.

Can some one tell me why mine doesnt work ive followed the same example step by step but my table doesnt filter based on the low Mid High slicer all the values are the same???

Thanks

Dan

DAX Formula Patterns.pbix (1.1 MB)

Because your Pricing Group column doesn’t have a relationship with Sales table.

If you want your measure to work in the current scenario with the current arrangement intact then use:

Total Sales = 
CALCULATE ( 
    SUM( Sales[Total Revenue] ),
    TREATAS (
        VALUES ( 'Groups For Pricing'[Pricing Group] ),
        Sales[Pricing Groups] 
    )
)

Or just create a relationship between Sales and Pricing Group and then everything works, but you still will have to make a change to the calculated column’s code in order to be able to create a relationship, otherwise Sales table would depend on ‘Groups For Pricing’ table for the Pricing Group column and ‘Groups For Pricing’ table will depend on Sales table for blank row, creating circular dependency.

Pricing Groups =
CALCULATE (
    DISTINCT ( 'Groups For Pricing'[Pricing Group] ),
    FILTER (
        'Groups For Pricing',
        Sales[Total Unit Price] >= 'Groups For Pricing'[Min Price]
            && Sales[Total Unit Price] < 'Groups For Pricing'[Max Price]
    )
)



@Greg has also put together a post on dynamic grouping, you can find that here:
It also includes links to relevant content on this subject, so you might want to explore that as well.

1 Like

Thanks @AntrikshSharma

Sam’s video doesn’t have a relationship with the sales table either though in his tutorial video 3 minutes 32 seconds in

Grouping with calculated column

Hi @Krays23, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Krays23, a response on this post has been tagged as “Solution”. 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 check box. Thanks!

Not really doesn’t explain why in sams video he doesn’t use relationships

Hi @Krays23,

You simply need to swap your Pricing Group column in your slicer and matrix table to use the Pricing Group column from your [Sales] table. Currently you are using the Pricing Group column from your [Groups For Pricing] table. As this has no physical relationship with your sales table, no change occurs.

If your Sales fact table was very large I would suspect that creating the physical relationship between table [Groups For Pricing] and [Sales] would perform better, as the slicer would simply be reading 3 records rather than having to check every distinct value in [Sales] before it can be populated.

I hope this helps,

Mark