Remove calculated values if underlying value exists in only one fact table

[Anonymous]

Hi All,

I have 2 fact tables and 2 dimension tables.

Fact table 1: 2019 cost

Fact table 2: 2022 cost

Dimension table 1: Cost Center 

Dimension table2: Date

My goal is to calculate the difference in cost between 2019 and 2022 and then visualize it by cost center and week; however, if a cost center or a date is missing from one of the fact tables then I don't want to show that difference in my visual.

For example, cost center 123 doesn't exist in week 1 of 2019, but it has a cost of $100 in week 1 of 2022. I don't want to include this cost center in my visual as a $100 increase in cost in week 1. I want to exclude this cost center from my visual for week 1 and have it show blank.

Below is my DAX Calculation as well as my data model. My dimension tables include all cost centers and dates.

Cost DIFF:= '2022'[Cost] - '2019'[Cost]

Is it possible to remove the difference in cost values if the underlying cost value only exists in one of the fact tables?

Thank you!

[Anonymous]

This resolved the error and solved my problem. You are a genius. Thank you @adudani 

[adudani]

@Anonymous ,

try 

Cost DIFF:=
IF (
ISBLANK('2022'[Cost]) || ISBLANK('2019'[Cost]),
BLANK(),
'2022'[Cost] - '2019'[Cost]
)

appreciate a thumbs up is this is helpul.

Please let me know if this resolves the question

[Anonymous]

@adudani I'm getting the following error with the logic you provided:

Semantic Error: A singal value for column 'Cost' in table '2022' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.

[adudani · Accepted solution]

@TJIK

This should resolve the error

Measure= 

Var 2019cost= Sum('2019'[Cost])

Var 2022cost= Sum('2022'[Cost])

 Var _CostDIFF=

IF (ISBLANK([2022cost] )|| ISBLANK([2019cost]),

BLANK(),

[2022cost]-[2019cost]

)

Return 

_CostDIFF