clarify how propagation works (matrix adds and unexpected empty row)

[PowerBeeTree]

This is a question i have seen many times but the answers provided in other posts don't help me understand what is potentially wrong.

The purpose of the examplte below is to clarify any confussion i may have around the propagation concept therefore let's use the following example.

Assume I have the following sales_table:

      date     product  sales

03/01/2024	A	3
03/02/2024	A	5
03/03/2024	A	8
03/04/2024	A	20
03/05/2024	A	2
03/04/2024	A	0
03/05/2024	A	7
03/06/2024	A	7
03/07/2024	A	11
03/08/2024	A	12
03/09/2024	A	6
03/10/2024	A	8
03/11/2024	A	18
03/12/2024	A	2
03/13/2024	A	5
03/14/2024	A	9
03/15/2024	A	6
03/03/2024	B	70
03/07/2024	B	75
03/08/2024	B	80
03/12/2024	C	40
03/14/2024	C	48

I want to filter (get in a matrix format) the product level sales using another table as the filtration layer and for any unmatched values to get a BLANK value. Continuing on the example above let's suppose for illustration purposes i want to get the product level sales for a predefined set of dates (no particular rule):

here is the provided filtration table i am using:

cdate

03/01/2024

03/02/2024

03/03/2024

03/07/2024

03/08/2024

03/09/2024

03/14/2024

03/15/2024

03/20/2024

03/21/2024

And the relationship between the two tables:

From what i understand the relationship should be one to many from the "custom_dates" to the "sales_table" as the unique "cdate" will propagate following the arrow direction and filter the dates in the "date" column of the sales_table. However the output adds and extra row at the top which captures any sales for those dates of the "custom_dates" table that didnt exist in the "sales_table". Also some of the dates in the "cdates" that dont exist in the "date" column dont populate with blank values for each of the products.

When i change the cardinality to many:many the result is closer to the expected as the empty row is vanished although the total for product C is still including the nonmatched values (also non matching dates still not showing empty values). However I believe this should be wrong as i specified earlier that "cdates" are unique values and therefore need to always be the "one" side of the cardinality.

As a result i judge from the above that i might be missing something in terms how filtration and propagation works. 

Can someone please help (am beginner level obviously )?

[v-yilong-msft · Accepted solution]

Hi @PowerBeeTree ,

Maybe you can try this:

First of all, create a new table to merge this two table with the dax:

Table =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN ( 'sales_table', 'custom_dates' ),
        'sales_table'[date] = 'custom_dates'[cdate]
    ),
    "cdate", [cdate],
    "product", [product],
    "sales", [sales]
)

Then create a matrix, the result is as follow:

Best Regards

Yilong Zhou

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

[PowerBeeTree]

Thanks, it looks like i will have to merge the two table with one of the JOIN functions DAX provides.

Also here is another workaround:

After doing some research and experimentation looks like by default Power BI will show all rows regardless the type of connection between two tables. The only way to remove any unwanted ones is to address this through the filters.  By removing the Blanks from the rows for the specific visual i get all the values i expect in the rows and the total in the bottom is correct. 

This is in case someone finds it helpful.

[PowerBeeTree]

i figured how to show the BLANK rows for the non matching days. I had to activate the following field.

however the main question remains, why is that extra row added at the very top? shouldn't the propagation for the 1:many cardinality provide by definition a fix for it?