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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

SWITCH() Not Working Properly In Dynamic Measure

I have a working measure defined as such (note that [Forecast Volume] external measure is returning a result properly within the CALCULATE() filter context):

 

 

Current Period = 
/*
Define the current-year reporting period and apply to forecast period (or any other table related to Date table). 
Variables define filters on the Date table to show forecast only for months that sales exist in the current fiscal year. 
This also uses parameters to allow substitution of measures according to slicer selection.
*/
VAR latestInvocedDate =
    LOOKUPVALUE(
        'Date'[Date],
        'Date'[DateKey],
        CALCULATE(
            MAX('Invoiced Sales'[DateKey]),
            ALL('Invoiced Sales')
        )
    )
VAR FYStartDate =
    LOOKUPVALUE(
        'Date'[Fiscal Year Start Date],
        'Date'[DateKey],
        CALCULATE(
            MAX('Invoiced Sales'[DateKey]),
            ALL('Invoiced Sales')
        )
    )
VAR commonPeriod =
    FILTER(
        'Date',
        'Date'[Date] <= latestInvocedDate && 'Date'[Date] >= FYStartDate
    )

RETURN
    IF(
        SELECTEDVALUE('Date'[Fiscal Year]) = YEAR(FYStartDate) + 1,
        CALCULATE(
            [Forecast Volume],
            commonPeriod
        ),
        [Forecast Volume]
    )

 

 

... however, when I attempt to hook this into a slicer selection by defining a variable with SWITCH() used to select alternate external measures, the CALCULATE() filter context doesn't work:

 

 

Current Period = 
/*
Define the current-year reporting period and apply to forecast period (or any other table related to Date table). 
Variables define filters on the Date table to show forecast only for months that sales exist in the current fiscal year. 
This also uses parameters to allow substitution of measures according to slicer selection.
*/
VAR latestInvocedDate =
    LOOKUPVALUE(
        'Date'[Date],
        'Date'[DateKey],
        CALCULATE(
            MAX('Invoiced Sales'[DateKey]),
            ALL('Invoiced Sales')
        )
    )
VAR FYStartDate =
    LOOKUPVALUE(
        'Date'[Fiscal Year Start Date],
        'Date'[DateKey],
        CALCULATE(
            MAX('Invoiced Sales'[DateKey]),
            ALL('Invoiced Sales')
        )
    )
VAR commonPeriod =
    FILTER(
        'Date',
        'Date'[Date] <= latestInvocedDate && 'Date'[Date] >= FYStartDate
    )
VAR Target = 
	SWITCH( TRUE(),
			VALUES( 'Measure'[Measure Name] ) = "Revenue ($)", [Forecast $],
			VALUES( 'Measure'[Measure Name] ) = "Volume (#)", [Forecast Volume],
            VALUES( 'Measure'[Measure Name] ) = "Margin ($)", [Forecast Margin $],
			VALUES( 'Measure'[Measure Name] ) = "Margin (%)", ( [Forecast Margin %] * 100 ),
			[Forecast Volume]
		)

RETURN
    IF(
        SELECTEDVALUE('Date'[Fiscal Year]) = YEAR(FYStartDate) + 1,
        CALCULATE(
            Target,
            commonPeriod
        ),
        Target
    )

 

This doesn't break the measure, only the filter context. So the result of SWITCH() must not be passing the litteral [Forecast *] strings into the 'Target' variable. Can someone explain why and an alternative approach?

Thanks in advance!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks ... If you are referring to my last post, that was me just trying what was suggested by @lbendlin 

In the OP, I am specifying a VAR (Target) with SWITCH() containing VALUES(<column>), which should return a single colum like SQL "SELECT DISTINCT". If a row contains the result (e.g., "Revenue ($)"), then return the table [Forecast $] (which is a measure).

Then:

 

RETURN
    IF(
        SELECTEDVALUE('Date'[Fiscal Year]) = YEAR(FYStartDate) + 1,
        CALCULATE(
            Target,
            commonPeriod
        ),
        Target
    )

 

When I specify the actual table-measure in place of "Target", this measure returns the correct result, which is the filtered result of CALCULATE([Forecast $],commonPeriod) or just [Forecast $] as in the IF(). The problem is that the result of SWITCH() ... e.g., [Forecast $] is not treated the same as just directly substituting [Forecast $] in CALCULATE().

It should work, but the DAX Formatter is obviously parsing this syntax to produce an incorrect result that must not actually resolve to [Forecast $].

Ultimately, I am trying to dynamically swap out measures into a single measure according to a slicer selection rather than creating four measures that each pass a single measure with the SWITCH() existing inside a separate measure calling each of the four.

I was able to achieve the desired result by just replicating the measure in the OP 4x for each specific measure, but that seems redundant.

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

Ping me once you had the time to create the sample PBIX

Anonymous
Not applicable

Unfortunately, this will take me hours to prepare, and I can't afford the time. I have simply trashed my idea and implemented the longer workaround.

Thanks for your willingness to assist though.

V-pazhen-msft
Community Support
Community Support

@Anonymous 

The DAX expression is not written correct. 

 

1. You cannot using Switch inside CALCULATE()

2. VALUES() function return a single column table, you cannot compare a table with a TEXT sting "Revenue ($)", you can just use: [measure name] = ""Revenue ($)"

 

Please also explian what do you want to achieve when you create a sample pbix.

 

Best Regards

Paul Zheng _ Community Support Team

Anonymous
Not applicable

Thanks ... If you are referring to my last post, that was me just trying what was suggested by @lbendlin 

In the OP, I am specifying a VAR (Target) with SWITCH() containing VALUES(<column>), which should return a single colum like SQL "SELECT DISTINCT". If a row contains the result (e.g., "Revenue ($)"), then return the table [Forecast $] (which is a measure).

Then:

 

RETURN
    IF(
        SELECTEDVALUE('Date'[Fiscal Year]) = YEAR(FYStartDate) + 1,
        CALCULATE(
            Target,
            commonPeriod
        ),
        Target
    )

 

When I specify the actual table-measure in place of "Target", this measure returns the correct result, which is the filtered result of CALCULATE([Forecast $],commonPeriod) or just [Forecast $] as in the IF(). The problem is that the result of SWITCH() ... e.g., [Forecast $] is not treated the same as just directly substituting [Forecast $] in CALCULATE().

It should work, but the DAX Formatter is obviously parsing this syntax to produce an incorrect result that must not actually resolve to [Forecast $].

Ultimately, I am trying to dynamically swap out measures into a single measure according to a slicer selection rather than creating four measures that each pass a single measure with the SWITCH() existing inside a separate measure calling each of the four.

I was able to achieve the desired result by just replicating the measure in the OP 4x for each specific measure, but that seems redundant.

lbendlin
Super User
Super User

That is intriguing.  Can you provide a sanitized sample pbix?

Anonymous
Not applicable

Probably, but it would take me a while to prepare that, as the file I'm working with has several tables, each with a lot of data. Scrubbing whilst maintaining functionality will be potentially challenging.

lbendlin
Super User
Super User

move the switch statement inside the calculate.

 

HOWEVER.  You are not showing the code for these measures. So we have no idea if the CALCULATE might interfere with the measure code.

 

Ideally you would not want to use nested measures. Rather be verbose and inline all code. It may not look as neat, but it gives you much more visibility and control over context transitions.

Anonymous
Not applicable

@lbendlin 

Thanks for the suggestion. Unfortunately, the result is the same using:

 

...
RETURN
    IF(
        SELECTEDVALUE('Date'[Fiscal Year]) = YEAR(FYStartDate) + 1,
        CALCULATE(
            SWITCH( TRUE(),
                    VALUES( 'Measure'[Measure Name] ) = "Revenue ($)", [Forecast $],
                    VALUES( 'Measure'[Measure Name] ) = "Volume (#)", [Forecast Volume],
                    VALUES( 'Measure'[Measure Name] ) = "Margin ($)", [Forecast Margin $],
                    VALUES( 'Measure'[Measure Name] ) = "Margin (%)", ( [Forecast Margin %] * 100 ),
                    [Forecast Volume]
                ),
            commonPeriod
        ),
        SWITCH( TRUE(),
                VALUES( 'Measure'[Measure Name] ) = "Revenue ($)", [Forecast $],
                VALUES( 'Measure'[Measure Name] ) = "Volume (#)", [Forecast Volume],
                VALUES( 'Measure'[Measure Name] ) = "Margin ($)", [Forecast Margin $],
                VALUES( 'Measure'[Measure Name] ) = "Margin (%)", ( [Forecast Margin %] * 100 ),
                [Forecast Volume]
            ),
    )

 

The measures called here are very simple as like:

 

Forecast Volume = SUM(Forecast[Vial Count])

 

The switch works with the referenced measures, and the calculations work, but 'currentPeriod' filter is ignored as if the second IF() argument always prevails.

I read here that the DAX Formatter may be translating the SWITCH() arguments into CALCULATE() as well, which may negate: https://www.sqlbi.com/articles/parameters-in-dax-measures/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors