Forum Discussion

tgjones43's avatar
tgjones43
Icon for Helper IV rankHelper IV
7 years ago
Solved

DAX calculation depending on values in 3 columns

Hi all   I need to create a measure in DAX that multiplies the value in [Count] by a fixed number (not shown here) that is dependent on the values in 3 other columns. [Width] is a numeric column, w...
Need Help
  • tex628's avatar
    tex628
    7 years ago
    Measure =
VAR Width = IF(Selectedvalue([Width])<5;"A";"B")
VAR Species = Selectedvalue([Species])
VAR Age = Selectedvalue([Age])
Return
IF(
Width = "A" &&
Species = "A" &&
Age = "A" ; 1 ;
IF(
Width = "A" &&
Species = "A" &&
Age = "B" ; 2 ;
IF(
Width = "A" &&
Species = "B" &&
Age = "B" ; 3 ;
IF(
Width = "B" &&
Species = "B" &&
Age = "B" ; 4 ;
IF(
Width = "B" &&
Species = "B" &&
Age = "A" ; 5 ;
IF(
Width = "B" &&
Species = "A" &&
Age = "A" ; 6 ;
IF(
Width = "B" &&
Species = "A" &&
Age = "B" ; 7 ;
IF(
Width = "A" &&
Species = "B" &&
Age = "A" ; 8 ; BLANK()
))))))))

    Replace the numeric values in red with your fixed values

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
7 years ago
Measure =
VAR Width = IF(Selectedvalue([Width])<5;"A";"B")
VAR Species = Selectedvalue([Species])
VAR Age = Selectedvalue([Age])
Return
IF(
Width = "A" &&
Species = "A" &&
Age = "A" ; 1 ;
IF(
Width = "A" &&
Species = "A" &&
Age = "B" ; 2 ;
IF(
Width = "A" &&
Species = "B" &&
Age = "B" ; 3 ;
IF(
Width = "B" &&
Species = "B" &&
Age = "B" ; 4 ;
IF(
Width = "B" &&
Species = "B" &&
Age = "A" ; 5 ;
IF(
Width = "B" &&
Species = "A" &&
Age = "A" ; 6 ;
IF(
Width = "B" &&
Species = "A" &&
Age = "B" ; 7 ;
IF(
Width = "A" &&
Species = "B" &&
Age = "A" ; 8 ; BLANK()
))))))))

Replace the numeric values in red with your fixed values

tgjones43's avatar
tgjones43
Icon for Helper IV rankHelper IV
7 years ago

Thanks all for your help.

 

The solution by tex628 is the one that worked best for me, although I had to make a small alteration to lines 3 and 4 to include the condition, as below:

 

Measure =
VAR Width = IF(SELECTEDVALUE([Width])<5,"A","B")
VAR Species = IF(SELECTEDVALUE([Species])="Atlantic salmon","A","B")
VAR Age = IF(SELECTEDVALUE([Age])="0+","A","B")
Return
IF(
Width = "A" &&
Species = "A" &&
Age = "A" , 0.5395 ,
IF(
Width = "A" &&
Species = "A" &&
Age = "B" , 0.6643 ,
IF(
Width = "B" &&
Species = "A" &&
Age = "A" , 0.5106 ,
IF(
Width = "B" &&
Species = "A" &&
Age = "B" , 0.5547 ,
IF(
Width = "A" &&
Species = "B" &&
Age = "A" , 0.5932,
IF(
Width = "A" &&
Species = "B" &&
Age = "B" , 0.6754 ,
IF(
Width = "B" &&
Species = "B" &&
Age = "A" , 0.5666 ,
IF(
Width = "B" &&
Species = "B" &&
Age = "B" , 0.6272 , BLANK()
))))))))