Filtering Dynamic Segmentation Count with another Dynamic Category

[Jellybean69]

Hello PowerBI Experts,

I am currently facing a challenges in PowerBI that i have be stuck for weeks. Would really hope some enlighten from the experts in this community

In the Summary View Sheet (1st Pic), both Product Category and Sts_Summary are derived from a Switch Measure as they are required to be dynamic because we need to filter by the parameter. I manage to countrows for the product category by creating a disconnected table and then link with the measure. However, i need to further filter this total count of product category by Sts Code which is a also a dynamic measure.

Click link to download Pbix file. Sorry couldn't find the attached button. link.

This is the formula i have been use to countrows for the product category which is a dynamic switch measure (dynamic segmentation). How do i alter this formula to filter the total by STS code.

ProductCateory_Link =

VAR SUMMARYTABLE = SUMMARIZE('Dataset','Dataset'[CUSTOMER ID],'Dataset'[PERD_ID])

RETURN

COUNTROWS(FILTER(SUMMARYTABLE, [Product Category]=MAX('Product Summary'[Product Category])))

Also is there a improve version of code, as i realized if my data when over 3 million rows. the calculation becomes incorrect.
i read in the SQIBI Blog that SWITCH Function is computational expensive, will TREATAS be a better function for this scenario above. Any comments and suggestion will be most welcome and much appreciated.

[Jellybean69]

I managed to solve myself already. 

ProductCateory_Link2 =

VAR SUMMARYTABLE = SUMMARIZE('Dataset','Dataset'[CUSTOMER ID],'Dataset'[PERD_ID])

RETURN

CALCULATE(DISTINCTCOUNT('Dataset'[CUSTOMER ID]),FILTER(SUMMARYTABLE, [Product Category]=MAX('Product Summary'[Product Category]) && [Sts_Summary] = MAX('Sts Code'[Sts Code])))

[v-jianpeng-msft · Accepted solution]

Hi, @Jellybean69 

Your DAX has no problem, the following is the test results:

If you want to use TREATAS, you need to create a Sts_Summary column in the Dataset table and temporarily treat this column as the Sts code column for calculation in CALCULATE. This is usually used to establish a temporary calculation relationship between two tables that have no relationship. In your scenario with 3 million rows of data, creating calculated columns is obviously not advisable.
Also, be aware that overuse of TREATAS can lead to poor query performance because it disrupts the query optimizer's ability to optimize the query plan.

Here, I provide a possible DAX expression. Before using this expression, please ensure that the Sts_Summay column exists.

ProductCateory_Link3 =
VAR SUMMARYTABLE =
    SUMMARIZE ( 'Dataset', 'Dataset'[CUSTOMER ID], 'Dataset'[PERD_ID] )
VAR MaxPro =
    MAX ( 'Product Summary'[Product Category] )
VAR Maxcode =
    MAX ( 'Sts Code'[Sts Code] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Dataset'[CUSTOMER ID] ),
        FILTER (
            SUMMARYTABLE,
            [Product Category] = MaxPro
                && [Sts_Summary]
                    = CALCULATE (
                        MAX ( 'Sts Code'[Sts Code] ),
                        TREATAS ( [Sts_Summary], 'Sts Code'[Sts Code] )
                    )
        )
    )

If you want to know about the use of TREATAS, you can click the link below:

TREATAS:https://dax.guide/treatas/

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.