Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
55 | |
54 | |
37 | |
29 |
User | Count |
---|---|
77 | |
62 | |
45 | |
40 | |
40 |