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 hav...
General Comment
Need Help
Show and Tell
Tutorial Requests
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