Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

When relationship is not found set value to 100

Hello Experts,

 

Im running in to the following problem: I have 2 tables:

Calender

Date datekey More calender stuff
1/1/2020 01012020 etc
1/2/2020 01022020 etc
1/3/2020 01032020 etc

 

values

Datekey value
1012020 55
1032020 12

 

They ofcourse have a relationship on datekey.

I created visuals based on the values. But now i want the following. 

If the datekey exists in calender table but not in the values table it should show a value of 100. 

I tried using EXCEPT but i couldnt get it to work. Anyone of you who did this before and can help me out? 

5 REPLIES 5
amitchandak
Super User
Super User

@Anonymous , try like

 Measure  = if (sum(Table[Value])+0=0,100, sum(Table[Value]))+0

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

This indeed could work. But i figured out that there are also values of 0. And that would result in more 100's then we would like. 

@Anonymous 

 

Try using ISBLANK

 

Measure  = IF(ISBLANK(SUM(Table[Value])),100, SUM(Table[Value]))


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

This works indeed. I thought isblank wont work because there is no record anyway. but it does 😄

Anonymous
Not applicable

How about this?

 

= IF(ISNUMBER(SUM([value]), 100,0)
 
 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.