Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

DAX expression - nested Ifs or variables?

UPDATED** I am looking for an efficient way to accomplish the following sudo in DAX measure in Power BI, any help would be appreciated!

 

measure =
If Table1 column1 = "Term1"
Then Table2 Coloum1 * Table3 column1
return value 
 
If returned results > Table1 coloum2
then Table2 Coloum1 * Table3 column1  / Table1 Coloum3
return value
 
If returned results < Table1 coloum2
return "0"
 
 
 
If Table1 column1 = "Term2"
Then Table1 Coloum4 * Table1 Coloum3
return value
  
else if
table1 coloum1 = "Term3"
retun "x"
Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Greg,

       

      Thank you for the suggestion, i will research in the SWITCH(TRUE(),..) direction.

       

      I updated the sudu for Term1, it was not quite right and is more complicated that the other two terms.  If anyone wants to take a stab at it so i have something to work off of, that would be very appreciated!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        I came up with this but my report is not showing all reqults in the measure column, i beleive it is because if the MIN, i tried to use SUMX  but the value is string

         

        Measure =


        VAR FreqType =
        MIN ( AccActionOptionRateFreqs[FrequencyType] )
        VAR QuanCalc =
        SUMX ( ContractItems, ContractItems[CurrentQuantity] )
        * SUMX (
        ContractProjectItemMaterialSetMaterials,
        ContractProjectItemMaterialSetMaterials[ConversionFactor]
        )
        VAR ContCalc =
        SUMX ( AccActionOptionRateFreqs, AccActionOptionRateFreqs[ActionRate] )
        * SUMX ( AccActionOptionRateFreqs, AccActionOptionRateFreqs[ActionFrequency] )
        VAR Conver =
        SUMX ( ContractItems, ContractItems[CurrentQuantity] )
        * SUMX (
        ContractProjectItemMaterialSetMaterials,
        ContractProjectItemMaterialSetMaterials[ConversionFactor]
        )
        VAR ActFreq =
        SUMX ( AccActionOptionRateFreqs, AccActionOptionRateFreqs[ActionFrequency] )
        VAR MinCalc = ( Conver / ActFreq )
        RETURN


        IF (
        FreqType = "Quantity",
        QuanCalc,
        IF (
        QuanCalc
        > ( SUMX ( AccActionOptionRateFreqs, AccActionOptionRateFreqs[MinQtyRequired] ) ),
        MinCalc,
        IF (
        QuanCalc
        < ( SUMX ( AccActionOptionRateFreqs, AccActionOptionRateFreqs[MinQtyRequired] ) ),
        "0",
        IF (
        FreqType = "Contract", ContCalc,
        IF (
        FreqType = "Temporal", "x", " " ) )
        )
        )
        )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago
    Tried Switch True and reeived the following error "swith does not support comparing values of type True/False with values of type Text
     
    RequiredTests =

    VAR FreqType = SELECTEDVALUE(AccActionOptionRateFreqs[FrequencyType])
    Return

    SWITCH (
        TRUE(),
    FreqType, "Quantity", SUMX(ContractItems, ContractItems[CurrentQuantity]) * SUMX(ContractProjectItemMaterialSetMaterials, ContractProjectItemMaterialSetMaterials[ConversionFactor]),
    FreqType, "Contract", SUMX(AccActionOptionRateFreqs, AccActionOptionRateFreqs[ActionRate]) * SUMX(AccActionOptionRateFreqs, AccActionOptionRateFreqs[ActionFrequency]),
    FreqType, "Temporal", "x")