Forum Discussion

Chanise89's avatar
Chanise89
Helper I
2 years ago

Find Variance based on selected values in Field Parameters

Good Afternoon Everyone,

I have a good one here! 

 

I have a requirement to allow users the ability to create their own Custom Variances exactly as the image below (See image 1)

 

1. I have created 2 Field Parameters that holds all of the measures used to populate the table and that part works as expected!

 

The issue I am running into is I need to create a Variance column based on what the user selects from Slicer A minus Slicer B. (refer to Image 1). I've tried different measures but I can't get it to work. 

 

Also I think it's important to note the fields used to create these measures:

 

Please, If anyone has any insight - it will be greatly appreciated! 

Thank You

General Comment
Need Help
Show and Tell
Tutorial Requests

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Chanise89 Well, this is a brute force way to do it. I can't think of anything elegant since most things I have tried around this lead to composite key errors or would require the use of EVALUATE.

    Variance = 
    VAR __Var1 = MAX('Variance 1'[Variance 1])
    VAR __Var2 = MAX('Variance 2'[Variance 2])
    VAR __Result = 
        SWITCH(TRUE(),
            __Var1 = "Measure 1" && __Var2 = "Measure 2", [Measure 1] - [Measure 2],
            __Var1 = "Measure 1" && __Var2 = "Measure 3", [Measure 1] - [Measure 3],
            __Var1 = "Measure 2" && __Var2 = "Measure 1", [Measure 2] - [Measure 1],
            __Var1 = "Measure 2" && __Var2 = "Measure 3", [Measure 2] - [Measure 3],
            __Var1 = "Measure 3" && __Var2 = "Measure 1", [Measure 3] - [Measure 1],
            __Var1 = "Measure 3" && __Var2 = "Measure 2", [Measure 3] - [Measure 2]
        )
RETURN
    __Result
  • Chanise89's avatar
    Chanise89
    Helper I
    2 years ago

    Greg_Deckler I see where you are going with this. With this approach, would this require me to add in all the possible selcetions that a user can select? 

      • Chanise89's avatar
        Chanise89
        Helper I
        2 years ago

        So I am still working through this & I just read your article on your dislike of CALCULATE. 

        Is there another way to write this statement w/o the use of CALCULATE:

        Q122_Actuals = CALCULATE(
        SUM('GL Data'[ACTUAL_AMOUNT]),
          'GL Data'[VCH_QUARTER] = "Q1",
           'GL Data'[N_YEAR] = "2022")
         
        I think I have another approach, however I keep running into this error message "DAX comparison operations do not support comparing values of type true/false"