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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Two measures exclude nulls when in a matrix

I have two tables like the following:

Loyalty and Rating CRM:

DCSS IDDCSS Rep IdDCSS ID + Rep IdNameLoyaltyRating
1000110010001+100BobA$100,000+
1000210010002+100SueA$10,000+
1000310110003+101DanielF$100+
1000410510004+105TerryB$1000+
1000510810005+108SteveF$0+
1000610810005+108PaulF$0+


Loyalty and Rating Data Warehouse

DCSS IdDCSS Rep IdDCSS ID + Rep IdDonation_Fiscal_YearDonation_Amount
1000110010001+1002020$100,000
1000110010001+1002021$50,000
1000210010002+1002020$10,000
1000310110003+1012020$100
1000310110003+1012021$10,000
1000410510004+1052020$1,000
1000510810005+1082021$100

 

They are joined on [DCSS ID + Rep Id]. I have measures like the following:

FY20 Actuals =
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2020)
FY21 Actuals =
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2021)


If I have one of the measures, the blank entries are included for that measure. For example, Sue only gave in 2020, if I use the FY21 Actuals, the row for her would have a blank value. When I have both the measures, I lose that row, for example, Paul has no donations but I still want to show his name.

What I want to see is:

 FY21 ActualsFY20 Actuals
Bob$50,000$100,000
Sue$0$10,000
Daniel$10,000$100
Terry$0$1,000
Steve$100$0
Paul$0

$0

 

What I'm seeing is Paul is missing and blank values where I'd like a $0:

 

 FY21 ActualsFY20 Actuals
Bob$50,000$100,000
Sue $10,000
Daniel$10,000$100
Terry $1,000
Steve$100 

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous 

Your measure is correct, it will show blank if there is no value in your ’Loyalty and Rating Data Warehouse‘.

Click Name column in Value Field in table visual and select show items with no data.

Now you can see the blank value.

1.png

If you want to show 0 when the value is blank, you can update your measure as [Measure]+0.

FY20 Actuals = 
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation_Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2020)+0
FY21 Actuals = 
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation_Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2021)+0

 Result is as below.

3.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous 

Your measure is correct, it will show blank if there is no value in your ’Loyalty and Rating Data Warehouse‘.

Click Name column in Value Field in table visual and select show items with no data.

Now you can see the blank value.

1.png

If you want to show 0 when the value is blank, you can update your measure as [Measure]+0.

FY20 Actuals = 
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation_Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2020)+0
FY21 Actuals = 
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation_Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2021)+0

 Result is as below.

3.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Vera_33
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

Use COALESCE outside your measure:

FY20 Actuals =
COALESCE(CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2020),0)

FY21 Actuals =
COALESCE(CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2021),0)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.