cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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!

1 ACCEPTED SOLUTION
Super User

@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

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
4 REPLIES 4
Anonymous
Not applicable

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

Super User

@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

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
Anonymous
Not applicable

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

Super User

@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

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.