Forum Discussion
Remove calculated values if underlying value exists in only one fact table
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!
@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
4 Replies
- adudani
Memorable Member
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
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
@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
This resolved the error and solved my problem. You are a genius. Thank you adudani