Exclude rows with blank values from non-exisiting combinations in data

[JerryAZ]

Hello,

I am developing a Power BI report, where two tables are used; A Fact table (FactTable) and a Date table (DatumTabel). Between the tables an active one-to-many relationship exists between the key fields DatumTabel [Date] en FactTable[1e Wijzigingsdatum].

The FactTable consists of a couple of columns that are used:

Ordernumber	Alert Reason	Product	1e Wijzigingsdatum
1	Reason 1	Home	1 May 2023
2	Reason 2	Home	4 April 2012
3	Reason 3	Traffic	5 June 2015
4	Reason 3	Traffic	6 September 2020
5	Reason 4	Travel	5 December 2022
6	Reason 4	Travel	1 January 2023
6	Reason 5	Travel	1 January 2023

Multiple Alert Reasons can exist within a single Ordernumber. The same alert reason can also exist within multiple Products.

For each date from the Date table, I would like to count the number of Orders per Product and Alert Reason combination. If there are no Orders available in the FactTable for a specific combination, I would like to see ‘0’. This can be achieved with a simple measure: OrderCount =  COALESCE(COUNT(Ordernumber),0).

In the report I created a table visual that consists of the columns of the FactTable and the Date column from the Date table. It looks like the following;

DatumTabel[Date]	FactTable[Alert reason]	FactTable[Product]	OrderCount
16 january 2023	reason 1	Home	20
16 january 2023	Reason 2	Home	5
15 january 2023	Reason 3	Traffic	10
15 january 2023	Reason 4	Traffic	2
13 january 2023	Reason 4	Travel	1
12 january 2023	Reason 5	Travel	50
12 january 2023	Reason 6	Traffic	0
12 january 2023	Reason 3	Home
11 january 2023	Reason 2	Travel
5 january 2023	Reason 5	Home

This OrderCount measure works when the combination of FactTable[Product] en FactTable[Alert reason] exists in the fact table, but for combinations that do not exist in the data, a blank value is returned in the table visual (see the rows in red).

I would like to see for every possible date the existing value combinations of Product and Alert Reason (see green row). 

I would like to exclude these non-existing combinations (red rows) from the visual. I have tried several options. For example exclude blank values of OrderCount in the filter pane, use COUNTROWS > 0 in the OrderCount measure, but none of them work. The DAX CROSSJOIN function is not an option because of the amount of data.

How can we exclude these non-exisiting combinations from the table visual?

I hope someone can help me out. Thanks in advance!

Kind regards,

Jerry

[JerryAZ · Accepted solution]

Hi @Anonymous ,

Thank you for your response. Eventually the issue is resolved. Not clear what the actual fix is, but it works. Perhaps a combination of actions. Here are the steps that I took to resolve the issue.

- Format date field of the FactTable[1e Wijzingsdatum]

- Filtered the DatumTabel[Date] column with a start date > t-2 years and end date < Today() in the PQ-editor

- Refreshed both datasets

- Created a new table visualisations with the same columns.

Regards,

[Anonymous]

Hi @JerryAZ ,

If I understand correctly, it seems that you are trying to get the count of order number with the specific combination. Am I right? If yes, what does specific combinations mean? Could you please explain more details with the special examples and more raw data? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards