Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have two tables like the following:
Loyalty and Rating CRM:
DCSS ID | DCSS Rep Id | DCSS ID + Rep Id | Name | Loyalty | Rating |
10001 | 100 | 10001+100 | Bob | A | $100,000+ |
10002 | 100 | 10002+100 | Sue | A | $10,000+ |
10003 | 101 | 10003+101 | Daniel | F | $100+ |
10004 | 105 | 10004+105 | Terry | B | $1000+ |
10005 | 108 | 10005+108 | Steve | F | $0+ |
10006 | 108 | 10005+108 | Paul | F | $0+ |
Loyalty and Rating Data Warehouse
DCSS Id | DCSS Rep Id | DCSS ID + Rep Id | Donation_Fiscal_Year | Donation_Amount |
10001 | 100 | 10001+100 | 2020 | $100,000 |
10001 | 100 | 10001+100 | 2021 | $50,000 |
10002 | 100 | 10002+100 | 2020 | $10,000 |
10003 | 101 | 10003+101 | 2020 | $100 |
10003 | 101 | 10003+101 | 2021 | $10,000 |
10004 | 105 | 10004+105 | 2020 | $1,000 |
10005 | 108 | 10005+108 | 2021 | $100 |
They are joined on [DCSS ID + Rep Id]. I have measures like the following:
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 Actuals | FY20 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 Actuals | FY20 Actuals | |
Bob | $50,000 | $100,000 |
Sue | $10,000 | |
Daniel | $10,000 | $100 |
Terry | $1,000 | |
Steve | $100 |
Thanks!
Solved! Go to Solution.
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.
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.
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.
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.
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.
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.
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)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
71 | |
38 | |
29 | |
28 |
User | Count |
---|---|
99 | |
88 | |
62 | |
42 | |
39 |