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, with the condition being either <5 or >5. [Species] and [Age] are text columns that have either a value of "A" or "B". So there are 8 possible comibinations, as shown below.

 

WidthSpeciesAgeCount
3AA10
7AA10
3AB10
7AB10
3BA10
7BA10
3BB10
7BB10

 

I assume I need to use IF and AND statements but having had several attempts I cannot get it to work. Can anyone help please?

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

9 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    7 years ago

    One way would be to make a calculated column that's something like:

     

    ConstantLookupValue = 

    var widthcheck = if ([width] <5,0,1)

    var agecheck = if([Age]="A",0,2)

    var speciescheck = if([Species]="A",0,4)

    return widthcheck+agecheck+speciescheck

     

    This'll return a value between 0 and 7, that relates to each row in the table you have - then you can relate it to a static table with numbers 0-7 and the related constant you haven't shown

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

      Hi jthomson That looks good, but I need a modification for my data. The issue is that the [Width] column is from one table and [Age] and [Species] are from a different table and the tables are connected by a One to Many relationship, i.e. for each value of [Width] there are several values of [Age] and [Species].

       

      Is it still possible to do the calculation in this scenario?

       

      Thank you.

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, tgjones43 

        It could be done, Please share a simple sample pbix file and the expected output.

         

        Best Regards,
        Lin