Dynamically change column in a filter

[TMason]

Hi, I need help with a DAX problem.

I have a calculate measure that has a filter.

The calculate expression needs to stay the same, however the filter needs to dynamically change. I need to change which column I am filtering on, whilst maintaining the criteria to filter.
In the simplified example table below, I'd want a measure that counts the number of products sold for Product 3 if Product 1 is also Yes. I then want to dynamically change the measure (using some form of slicer) so that it still counts the number of Product 3 sold if Product 4 is Yes.

So in the first scenario the outcome would be 3 (because it's counting the number of yes's for Product 3, so AAA is excluded because it's a No & company CCC doesn't have a yes for product 1 and therefore is also excluded), while in the second Scenario the outcome would be 2 (because only companies DDD and EEE have a yes for product 4).

Company Name	Product 1	Product 2	Product 3	Product 4
AAA	Yes	No	No	No
BBB	Yes	No	Yes	No
CCC	No	Yes	Yes	No
DDD	Yes	No	Yes	Yes
EEE	Yes	Yes	Yes	Yes

[Anonymous · Accepted solution]

Hi @TMason ,

Please try below steps:

1. below is my test table

Table:

Table 2:

2. create a measure with below dax formula

Dynamic Product Count =
VAR SelectedProduct =
    SELECTEDVALUE ( 'Table'[Product], "Product 1" )
VAR FilterCondition =
    SWITCH (
        SelectedProduct,
        "Product 1",
            CALCULATE (
                COUNTROWS ( 'Table 2' ),
                'Table 2'[Product 1] = "Yes"
                    && 'Table 2'[Product 3] = "Yes"
            ),
        "Product 2",
            CALCULATE (
                COUNTROWS ( 'Table 2' ),
                'Table 2'[Product 2] = "Yes"
                    && 'Table 2'[Product 3] = "Yes"
            ),
        "Product 3", CALCULATE ( COUNTROWS ( 'Table 2' ), 'Table 2'[Product 3] = "Yes" ),
        "Product 4",
            CALCULATE (
                COUNTROWS ( 'Table 2' ),
                'Table 2'[Product 4] = "Yes"
                    && 'Table 2'[Product 3] = "Yes"
            ),
        CALCULATE ( COUNTROWS ( 'Table 2' ), 'Table 2'[Product 3] = "Yes" )
    )
RETURN
    FilterCondition

3. add a slicer with Table field, add a card visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.