Create a slicer based on a measure that can filter other measure

[Anonymous]

Hi

I am trying to create a dynamic filter that allows me to use a measure as a slicer.

I have created a measure that classifies the costumers based on the activities they engage in:

• Only activity A
• Only activity B
• Both A and B

As i also want to a filter based on the weeks, the coustumer typer might change based on the interval i look at (eg. costumer A1 engages in both activity A and B in the full period, but only in activity A in week 1) 

My measure is 

Measure1 =

                        VAR ACTIVITY_A =

                                CALCULATE(

                                SUM(Tab1[Length of activity]),

                                FILTER ( Tab1, Tab1[Type of good]=="A"))

                       

                         VAR ACTIVITY_B =

                                CALCULATE(

                                SUM(Tab1[Length of activity]),

                                FILTER ( Tab1, Tab1[Type of good]=="B"))

RETURN

SWITCH(

    TRUE(),

    ACTIVITY_A>0 && ACTIVITY_B=0, "Only A",
     ACTIVITY_A=0 && ACTIVITY_B>0, "Only B", "Both A and B")  

Now i want to use this measure as a slicer in my dashboard. 

E.g. i have another measure that i want to display in the dashboard, that calculates the numer of unique costumers.
Measure_costumers=Distinctcount(Tab1[Costumer-ID]). 

I want to use my measure1 as a filter on this.

Costumer -ID	Length of activity	Type of activity	Week
A1	10	A	1
A1	10	A	1
A1	20	A	1
A1	20	B	2
B1	10	A	1
B1	10	B	1
B1	20	A	2
B1	20	B	2

So far I have tried to first to create a disconnected table with the classification types (see below),

and then create a new measure that connects measure1 to this table

Filter Status =

VAR CurrentSelection = SELECTEDVALUE('Tab)'[Type])

VAR CurrentClass = [Measure1]

RETURN

SWITCH(

    TRUE(), 

CurrentSelection = "A" && CurrentClass = "A", 1 ,

    CurrentSelection = "B" && CurrentClass = "B", 1 ,

    CurrentSelection = "Both A and B" && CurrentClass = "Both A and B", 1 ,
     0)

Type	Order
A	1
B	2
Both A and B	3

But I still have problems to use my measure 1 as a filter on Measure_costumers. Any suggestions? 

[Anonymous]

It works fine, but if i also want to use week-number as a slicer, the Measure_costumers do not work (try to remove row 5). If i then click on week=1 and Type =1 the measure is still 2... 

[Anonymous]

Hi @Anonymous ,

Just need adjust the dax formula, please try below dax formula:

Measure =
VAR cur_week =
    SELECTEDVALUE ( 'Table'[Week] )
VAR cur_type =
    SELECTEDVALUE ( Table2[Type] )
VAR cur_idx =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _a =
    CALCULATETABLE (
        VALUES ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity] = "A"
                && [Week] = cur_week
        )
    )
VAR _b =
    CALCULATETABLE (
        VALUES ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity] = "B"
                && [Week] = cur_week
        )
    )
VAR _ab =
    CALCULATETABLE (
        VALUES ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity]
                IN { "A", "B" }
                && [Week] = cur_week
        )
    )
RETURN
    SWITCH (
        cur_type,
        "A", IF ( cur_idx IN _a, 1 ),
        "B", IF ( cur_idx IN _b, 1 ),
        "Both A and B", IF ( cur_idx IN _ab, 1 )
    )

Measure_costumers =
VAR cur_week =
    SELECTEDVALUE ( 'Table'[Week] )
VAR cur_type =
    SELECTEDVALUE ( Table2[Type] )
VAR _a =
    CALCULATETABLE (
        VALUES ( 'Table'[Costumer -ID] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity] = "A"
                && [Week] = cur_week
        )
    )
VAR _b =
    CALCULATETABLE (
        VALUES ( 'Table'[Costumer -ID] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity] = "B"
                && [Week] = cur_week
        )
    )
VAR _ab =
    CALCULATETABLE (
        VALUES ( 'Table'[Costumer -ID] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity]
                IN { "A", "B" }
                && [Week] = cur_week
        )
    )
RETURN
    SWITCH (
        cur_type,
        "A", COUNTROWS ( _a ),
        "B", COUNTROWS ( _b ),
        "Both A and B", COUNTROWS ( _ab )
    )

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.

[Anonymous]

Is there a way to do it, where the measure_costumers also works when you do not select  any of the weeks (and it therefore show all the weeks)? The reason is that i eventually want to add more slicer that are already part of my tabel (weekday/weekend and other characteristics)? Thank you! 

[Anonymous]

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table:

Table2:

2. create measure with below dax formula

Measure =
VAR _a =
    CALCULATETABLE (
        VALUES ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), [Length of activity] > 0 && [Type of activity] = "A" )
    )
VAR _b =
    CALCULATETABLE (
        VALUES ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), [Length of activity] > 0 && [Type of activity] = "B" )
    )
VAR _ab =
    CALCULATETABLE (
        VALUES ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity] IN { "A", "B" }
        )
    )
VAR cur_type =
    SELECTEDVALUE ( Table2[Type] )
VAR cur_idx =
    SELECTEDVALUE ( 'Table'[Index] )
RETURN
    SWITCH (
        cur_type,
        "A", IF ( cur_idx IN _a, 1 ),
        "B", IF ( cur_idx IN _b, 1 ),
        "Both A and B", IF ( cur_idx IN _ab, 1 )
    )

Measure_costumers =
VAR cur_type =
    SELECTEDVALUE ( Table2[Type] )
VAR _a =
    CALCULATETABLE (
        VALUES ( 'Table'[Costumer -ID] ),
        FILTER ( ALL ( 'Table' ), [Length of activity] > 0 && [Type of activity] = "A" )
    )
VAR _b =
    CALCULATETABLE (
        VALUES ( 'Table'[Costumer -ID] ),
        FILTER ( ALL ( 'Table' ), [Length of activity] > 0 && [Type of activity] = "B" )
    )
VAR _ab =
    CALCULATETABLE (
        VALUES ( 'Table'[Costumer -ID] ),
        FILTER (
            ALL ( 'Table' ),
            [Length of activity] > 0
                && [Type of activity] IN { "A", "B" }
        )
    )
RETURN
    SWITCH (
        cur_type,
        "A", COUNTROWS ( _a ),
        "B", COUNTROWS ( _b ),
        "Both A and B", COUNTROWS ( _ab )
    )

3. add a slicer with "Table2", add a table visual with "Table" fields, add "Measure" to table visual and apply, add a card visual with "Measure_costumers"

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.

[Anonymous]

Thank you!!