Returning Blanks / Missing Data / Matix or table with dates?

[jh292]

Hi team,

Struggling...

I have 4 tables;

Site Report - Contains monthly billing data on customers, and the products they buy. I only want to report on a set month and I apply this in a filter, but the table does contain info for multiple months (added to as time passes)

SiteID	Site Name	Product	Selling monthly	Date of Report
56	Customer A	Car	150	01/10/2020
56	Customer A	Bike	200	01/10/2020
354	Customer B	Car	100	01/10/2020
354	Customer B	Bike	35	01/10/2020
354	Customer B	Scooter	50	01/10/2020
200	Customer C	Bike	100	01/10/2020
56	Customer A	Car	150	01/09/2020
56	Customer A	Bike	200	01/09/2020
354	Customer B	Car	100	01/09/2020
354	Customer B	Bike	35	01/09/2020
354	Customer B	Scooter	50	01/09/2020
200	Customer C	bike	200	01/09/2020

Contract Details - A table containing "Contracts" (they are their own entity) with end dates 

ContractID	Associated SiteID	Contract Type	Start Date	End Date	Associated DocumentID
1	354	Car	28/07/2018	27/07/2019	50
2	56	Car	02/05/2020	01/05/2022	40
3	354	Bike	08/07/2014	07/07/2017	645

Document Details - again they are there own entities are are associated within our CRM to other entities, for this report they are associated to a SiteID & a ContractID

Document ID	Associated SiteID	Document Category
50	354	Signed
40	56	Not Signed
645	354	Signed

A customer table to hopefully link everything and stop many to many relationships (but may not be required)

SiteID	Site Name
56	Customer A
354	Customer B
200	Cusomter C

I am trying to product a Table or Matrix that will show whether a customer buys a product (it's spend on it), if we have a contract, (if we do then) it's Contract End date, and whether it's document is signed or not, as per below 

Bike

Car

Scooter

Spend

Contract?

End date

Signed?

Spend

Contract?

End date

Signed?

Spend

Contract?

End date

Signed?

Customer A

Customer B

Customer C

My issue seems to be because I dont have contracts (and therefore end dates) for every customer, and so my matrix or table (ive tried both) wont return information on Customer C at all. 

I dont really know what I have to write or add to the tables to get this info through I'm affraid. Can someone help? 

[v-alq-msft]

Hi, @jh292 

When creating a visual, Power BI only shows relevant data when creating a visual to properly manage how data is presented and displayed. Power BI determines which data is relevant based on the configuration of the visual, and the underlying data model. You may use the Show items with no data feature to enable you to do exactly that - include data rows and columns that don't contain measure data (blank measure values).

For further information, please refer to the document .

Best Regards

Allan

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

[Anonymous]

A real flaw in Power Bi that I see a lot - very good at presenting data that's there - but not very good at presenting data thats not.

You can try this...

Right click on one of your values and choose 'show items with no data' - This sometimes works for me.

Alternatively, try and populate the data with something (dummy date maybe) 

Thanks.