Forum Discussion

nunezjo's avatar
nunezjo
Frequent Visitor
3 years ago

Need help with DAX IF Statement: Dynamically setting parameter order based on a selected value

I'm currently working on a DAX formula in Power BI where I need to dynamically set the value of a parameter based on a selected value from a dropdown. Specifically, I am using this in the context of changing a time scale between "Fiscal Week" and "Fiscal Period".

 

FCL Per/Wk = 
    VAR selectedmeasure = 
    IF( SELECTEDVALUE('PARM Time Scale'[PARM Time Scale Order]) = 1, 
        SELECTEDVALUE('PARM FCL Per/Wk'[Parameter Order]) = 0, 
        SELECTEDVALUE('PARM FCL Per/Wk'[Parameter Order]) = 1
    )
    RETURN
    selectedmeasure

 

The idea here is that if 'PARM Time Scale'[PARM Time Scale Order] is 1 (which represents "Fiscal Week" in my case), then I want 'PARM FCL Per/Wk'[Parameter Order] to be 0 (representing "Fiscal Week"), otherwise, it should be 1 (representing "Fiscal Period").

However, I am not able to get the desired result using this DAX formula. I believe the issue might be with how I'm using the IF function or how I'm handling the selected value, but I'm not quite sure.

So in summary whenever I go to my trend axis filter and select Fiscal Week End I want it to change the slider where it contains the fcl period number or fcl week number, so using 2 paramters here one for trend axis which uses parm time scale, and one for the slider which using PARM FCL Per/Wk and I want one paramter to trigger the other. 

I would appreciate any assistance or guidance on how to achieve this. 

Thank you in advance!

 

 

 

Need Help

1 Reply

  • Chris_White's avatar
    Chris_White
    Resolver II
    3 years ago

    In DAX, the equals symbol is used in two different ways. Setting one thing equal to another, and testing for equality. In your FCL Per/Wk measure, the first two equals symbols are setting one thing equal to another and the last 3 are testing for equality. From your description it appears you are imagining that your IF statement is setting the value of [Parameter Order], and it's doing no such thing. It's testing whether [Parameter Order] is 0 or 1 and returning TRUE or FALSE accordingly.

     

    If you want a filter applied to the PARM Time Scale table to affect the PARM FCL Per/Wk table, create a relationship between them in your model. You will need to add another column to your PARM Time Scale table to achieve this (containing 0 or 1 accordingly).