Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 c...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
lbendlin
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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/