Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
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:
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
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
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?
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...
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.
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!
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.
Thank you!!