Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need support on PBI DAX

Hi all

 

Need your support on POWERBI DAX.

I am converting Tableau to PowerBI. 

Below is the Tableau calculation and I am trying in PBI DAX. can anyone please help me out.

 

 

1) Scenario 1

IF (NOT ISNULL([Colomn1]) OR [Colomn1] <> 0)
AND ISNULL([Colomn2])
THEN 0 ELSE ([[Colomn2])
END

 

I have written below DAX calculation, but not sure.

 

IF((NOT(ISBLANK([Colomn1]) || [Colomn1 <> 0]))
&& ISBLANK([Colomn2]),0,[Colomn2])
 
please correct me, if i am wrong.
 
2) 1) Scenario 2
 
SUM(IFNULL([Colomn 1],0)) - SUM([Colomn 5])
 

I have written below DAX calculation, but not sure.

COlomn X = IF(ISBLANK([Colomn 1]),0,[Colomn 1])

 

SUM([Colomn X]- SUM(Colomn 5])

 

please correct me.

 

thanks in advance

Need Help
Show and Tell
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    Your dax formulas in both senarios are correct. In addtion, you don't need to add a new column [Column X] in senario 2, you can use dax like below:

    Result =
SUMX ( FILTER ( Table, Table[Colomn 1] <> BLANK () ), [Column 1] )
    - SUM ( [Colomn 5] )

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

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

    Anonymous ,

     

    Your dax formulas in both senarios are correct. In addtion, you don't need to add a new column [Column X] in senario 2, you can use dax like below:

    Result =
SUMX ( FILTER ( Table, Table[Colomn 1] <> BLANK () ), [Column 1] )
    - SUM ( [Colomn 5] )

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-yuta-msft ,

       

      Still my end results are incorrect, below is the final calculation to find whether it is increased or decreased based on senario 2 result. 

       

      senario 2 Result = SUMX ( FILTER ( Table, Table[Colomn 1] <> BLANK () ), [Column 1] ) - SUM ( [Colomn 5]

       

      Tableau Calculation

      IF { FIXED [Project ID] : ([senario 2 result]) } > 0 THEN "Increased"
      ELSEIF { FIXED [Project ID] : ([senario 2 result]) } < 0 THEN "Decreased"
      ELSEIF { FIXED [Project ID] : ([senario 2 result)]) } = 0 THEN "No Change"
      ELSE NULL
      END

       

      PBI DAX

      Variance = CALCULATE( [senario 2 result)],ALLEXCEPT([Project ID]))
      Increased or Decreased Variance = IF([Variance]>0,"increased",IF([Variance]<0,"Decreased",IF([Variance]=0,"No change",Blank())))

       

      Final output should be as below in Table green and Table red is what i am getting in PBI

       

       

      Thank you in advance.

       

      Regards,

      Murali

      • Cmcmahan's avatar
        Cmcmahan
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        So looking at your measures and output, it should be working. 
        Assuming you're outputting [Variance] below the category, your DAX looks to be right.  You're getting the right values, but Project B is showing up in the wrong category.  Is [Increased or Decreased Variance] a measure or calculated column?  What visualization are you using to create the incorrect table?

         

        Is it possible for you to anonymize a data sample and share it? Or even a sample .pbix?