Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Problem related to If condition which is being applied on Calculated measure and gives absurd data

Hi,

 

Problem is related to If condition which is being applied on Calculated measure and gives absurd result.

Below are the two tables connected through Date Field [Date - Launch Date]

 

 

Now I have created a measure which is very simple "Average Measure Without If Condition"=AVERAGE(summary[Volume])

And one slicer "YEAR" added from Date Dim.

Please see below

 

Filter is working fine so far.

 

Now I have created one more measure .

Average Measure with If condition = IF (ISBLANK([Average Measure Without If Condition]),"TBD",FORMAT([Average Measure Without If Condition],"Currency")
 
After adding this measure to the grid it gives abusrd result .Looks like its doing cross joining.
I tried is with Calculate DAX function with filter condtion but that does not work either.
Please help.  Thanks In advance !!
 
Nitz
 
 
 
 
 
 
 

 

 

 

 

 

 

 

Need Help
  • Payeras_BI's avatar
    Payeras_BI
    4 years ago

    Hi again Anonymous ,

    I have tried to reproduce your issue on my own and found better solution where you can keep the Year column coming from the Date_Dim table and avoid the cross-join issue when replacing the blanks for something else.
    Issue:

     

    Solution:

    Measure:

    Average Measure with IF Condition + ISEMPTY =
VAR _Average = [Average Measure Without If Condition]
RETURN
    IF (
        ISEMPTY ( 'Summary' ),
        BLANK (),
        IF ( ISBLANK ( _Average ), "TBD", FORMAT ( _Average, "Currency" ) )
    )

    I hope this helps. 

    Regards,

     

3 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous ,

    Does the issue persist when, in your visualization, you remove the column Year coming from your Date_Dim table?

    I would create a measure, instead, to get the Year from the Launch Date column. 

    Year = YEAR(MAX(Summary[Launch Date]))

    Let me know the result. 

    Regards,

  • Payeras_BI's avatar
    Payeras_BI
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi again Anonymous ,

    I have tried to reproduce your issue on my own and found better solution where you can keep the Year column coming from the Date_Dim table and avoid the cross-join issue when replacing the blanks for something else.
    Issue:

     

    Solution:

    Measure:

    Average Measure with IF Condition + ISEMPTY =
VAR _Average = [Average Measure Without If Condition]
RETURN
    IF (
        ISEMPTY ( 'Summary' ),
        BLANK (),
        IF ( ISBLANK ( _Average ), "TBD", FORMAT ( _Average, "Currency" ) )
    )

    I hope this helps. 

    Regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Payeras_BI , Thank you so much for your help 😊 .It is working fine.

      Simple if was not working and writing this way solving the problem .Its really wierd.