Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm looking to create a measure that will populate based on a slicer selection. This measure will eventually be used as a slicer. Can anyone illuminate me on how to achieve this using DAX?
Base Table
Date | Identifier | Temperature |
1/1/2023 | Actual | 10 |
1/2/2023 | Actual | 20 |
1/3/2023 | Possible | 30 |
1/3/2023 | Scenario1 | 40 |
1/4/2023 | Possible | 50 |
1/4/2023 | Scenario2 | 20 |
1/5/2023 | Actual | 30 |
1/6/2023 | Actual | 40 |
1/7/2023 | Possible | 45 |
1/7/2023 | Scenario3 | 10 |
1/8/2023 | Actual | 20 |
New Table || User Selection: 1/3/2023
Logic of new measure: If userselection = Date then populate identifier, else if userselection <> Date and Identifier not like "%Scenario%" then populate "All", else leave blank
Date | Identifier | Temperature | New Measure |
1/1/2023 | Actual | 10 | All |
1/2/2023 | Actual | 20 | All |
1/3/2023 | Possible | 30 | Possible |
1/3/2023 | Scenario1 | 40 | Scenario1 |
1/4/2023 | Possible | 50 | All |
1/4/2023 | Scenario2 | 20 | |
1/5/2023 | Actual | 30 | All |
1/6/2023 | Actual | 40 | All |
1/7/2023 | Possible | 45 | All |
1/7/2023 | Scenario3 | 10 | |
1/8/2023 | Actual | 20 | All |
Solved! Go to Solution.
@otto-user101 , To achieve your goal of creating a dynamic measure that responds to slicer selections, it's essential to use a disconnected date table for the slicer. This ensures that your measure can respond independently to the slicer selection without being directly tied to the dates in your base table.
Then you can have measures like
Dynamic Measure =
VAR SelectedDate = SELECTEDVALUE(DisconnectedDateTable[Date])
VAR MaxDate = CALCULATE(MAX(BaseTable[Date]), ALL(BaseTable))
RETURN
SWITCH(
TRUE(),
SelectedDate = MaxDate && BaseTable[Identifier] = "Possible", "Possible",
SelectedDate <> MaxDate && NOT CONTAINSSTRING(BaseTable[Identifier], "Scenario"), "All",
BLANK()
)
Hi @otto-user101 ,
Please try below steps:
1.below is my test table
Base Table:
Table:
Table = CALENDAR(FIRSTDATE('Base Table'[Date]),LASTDATE('Base Table'[Date]))
2. create a meausre with below dax formula
Measure =
VAR _a =
SELECTEDVALUE ( 'Table'[Date] )
VAR _b =
SELECTEDVALUE ( 'Base Table'[Date] )
VAR _c =
SELECTEDVALUE ( 'Base Table'[Identifier] )
VAR _result =
SWITCH (
TRUE (),
_a = _b, _c,
_a <> _b
&& NOT ( CONTAINSSTRING ( _c, "Scenario" ) ), "All",
BLANK ()
)
RETURN
_result
3. add a table visual with Base Table fields and measure, add a slicer visual with Table field
Best Regards,
Liz Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-lulu2-msft @amitchandak I appreciate your help! I submitted another question to better explain my desired outcome.
Hi @otto-user101 ,
Please try below steps:
1.below is my test table
Base Table:
Table:
Table = CALENDAR(FIRSTDATE('Base Table'[Date]),LASTDATE('Base Table'[Date]))
2. create a meausre with below dax formula
Measure =
VAR _a =
SELECTEDVALUE ( 'Table'[Date] )
VAR _b =
SELECTEDVALUE ( 'Base Table'[Date] )
VAR _c =
SELECTEDVALUE ( 'Base Table'[Identifier] )
VAR _result =
SWITCH (
TRUE (),
_a = _b, _c,
_a <> _b
&& NOT ( CONTAINSSTRING ( _c, "Scenario" ) ), "All",
BLANK ()
)
RETURN
_result
3. add a table visual with Base Table fields and measure, add a slicer visual with Table field
Best Regards,
Liz Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@otto-user101 , To achieve your goal of creating a dynamic measure that responds to slicer selections, it's essential to use a disconnected date table for the slicer. This ensures that your measure can respond independently to the slicer selection without being directly tied to the dates in your base table.
Then you can have measures like
Dynamic Measure =
VAR SelectedDate = SELECTEDVALUE(DisconnectedDateTable[Date])
VAR MaxDate = CALCULATE(MAX(BaseTable[Date]), ALL(BaseTable))
RETURN
SWITCH(
TRUE(),
SelectedDate = MaxDate && BaseTable[Identifier] = "Possible", "Possible",
SelectedDate <> MaxDate && NOT CONTAINSSTRING(BaseTable[Identifier], "Scenario"), "All",
BLANK()
)
User | Count |
---|---|
106 | |
89 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |