Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
Anonymous
Not applicable

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

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 -IDLength of activityType of activityWeek
A110A1
A110A1
A120A1
A120B2
B110A1
B110B1
B120A2
B120B2

 

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)
 
TypeOrder

A

1
B2
Both A and B3

 

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

5 REPLIES 5
Anonymous
Not applicable

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
Not applicable

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 )
    )

vbinbinyumsft_0-1676539932105.png

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
Not applicable

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
Not applicable

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1676515480214.png

 

Table2:

vbinbinyumsft_1-1676515496913.png

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"

vbinbinyumsft_2-1676515681857.png

vbinbinyumsft_3-1676515697710.png

vbinbinyumsft_4-1676515729507.pngPlease 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
Not applicable

Thank you!!

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors