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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
otto-user101
Frequent Visitor

Create dynamic measure based on slicer selection

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

DateIdentifierTemperature
1/1/2023Actual10
1/2/2023Actual20
1/3/2023Possible30
1/3/2023Scenario140
1/4/2023Possible50
1/4/2023Scenario220
1/5/2023Actual30
1/6/2023Actual40
1/7/2023Possible45
1/7/2023Scenario310
1/8/2023Actual20

 

 

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

DateIdentifierTemperatureNew Measure
1/1/2023Actual10All
1/2/2023Actual20All
1/3/2023Possible30Possible
1/3/2023Scenario140Scenario1
1/4/2023Possible50All
1/4/2023Scenario220 
1/5/2023Actual30All
1/6/2023Actual40All
1/7/2023Possible45All
1/7/2023Scenario310 
1/8/2023Actual20All
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

 

View solution in original post

v-lulu2-msft
Employee
Employee

Hi @otto-user101 ,

Please try below steps:
1.below is my test table

 Base Table:

vbinbinyumsft_1-1703847984793.png

 

Table:

 

Table = CALENDAR(FIRSTDATE('Base Table'[Date]),LASTDATE('Base Table'[Date]))

 

 

vlulu2msft_4-1703843943168.png

 

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

vbinbinyumsft_0-1703847899398.png

 

 

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.

View solution in original post

3 REPLIES 3
otto-user101
Frequent Visitor

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

v-lulu2-msft
Employee
Employee

Hi @otto-user101 ,

Please try below steps:
1.below is my test table

 Base Table:

vbinbinyumsft_1-1703847984793.png

 

Table:

 

Table = CALENDAR(FIRSTDATE('Base Table'[Date]),LASTDATE('Base Table'[Date]))

 

 

vlulu2msft_4-1703843943168.png

 

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

vbinbinyumsft_0-1703847899398.png

 

 

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
Super User
Super User

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.