Dynamically change column in a filter

Question

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 &amp; 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).&nbsp;Company NameProduct 1Product 2Product 3Product 4AAAYesNoNoNoBBBYesNoYesNoCCCNoYesYesNoDDDYesNoYesYesEEEYesYesYesYes&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
Please try below steps:
1. below is my test table
Table:

Table 2:

&nbsp;
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"
                    &amp;&amp; 'Table 2'[Product 3] = "Yes"
            ),
        "Product 2",
            CALCULATE (
                COUNTROWS ( 'Table 2' ),
                'Table 2'[Product 2] = "Yes"
                    &amp;&amp; '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"
                    &amp;&amp; 'Table 2'[Product 3] = "Yes"
            ),
        CALCULATE ( COUNTROWS ( 'Table 2' ), 'Table 2'[Product 3] = "Yes" )
    )
RETURN
    FilterCondition

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

Please refer the attached .pbix file.
&nbsp;
Best regards,Community Support Team_Binbin YuIf this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Dynamically change column in a filter

1 Reply

Related content

Power BI - Dynamically Change Column Name

Change column types dynamically

Dynamic Column headers

Dynamic Column Name

Dynamic Variable in Calculated Columns

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