Create dynamic measure based on slicer selection

[otto-user101]

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

[amitchandak · Accepted 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()
)

[otto-user101]

@v-lulu2-msft @amitchandak I appreciate your help! I submitted another question to better explain my desired outcome. 

[v-lulu2-msft · Accepted solution]

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.

[amitchandak · Accepted 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()
)