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

[Anonymous]

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

[Payeras_BI · Accepted solution]

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]

@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.

[Payeras_BI]

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,