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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

Anonymous Use a SWITCH( TRUE(), ... ) statement

  • 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", " " ) )
      )
      )
      )