SWITCH() Not Working Properly In Dynamic Measure

[Anonymous]

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!

[lbendlin]

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

[Anonymous]

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]

@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 · Accepted solution]

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]

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

[Anonymous]

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]

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]

@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/