How to force one-side of relationship to appear when there is no corresponding record on many side.

Anonymous — Sun, 28 Feb 2021 04:32:07 GMT

I have the following tables:

The measure Latest Sales correctly gives me the latest sum for each Sales Person:

Latest Sales = VAR MaxKnownDate = MAX ( Sales[DateOfSale] ) VAR SalesSummaryBase = SUMMARIZE ( 'SalesPerson' ,'SalesPerson'[Name] ) VAR SalesSummary = CALCULATETABLE ( ADDCOLUMNS ( SalesSummaryBase, "Latest Count", CALCULATE ( SUM ( Sales[Amount] ), LASTNONBLANK ( 'Date'[Date], COUNTROWS(RELATEDTABLE(Sales)) ) ) ) ,FILTER ( ALL ( Sales[DateOfSale] ), Sales[DateOfSale] <= MaxKnownDate ) ) VAR Result = SUMX(SalesSummary, [Latest Count]) RETURN Result

Here's the result:

Sales Person Id Name Latest Sales

3 Joe $5

1 John $22

2 Mary $65

The Sales Person slicer shows all Sales Persons.

The sales person Susie does not appear in the report because there are no sales record for Susie. How can I force Susie to show up on the report under the Sales Person ID column with an amount of $ 0.00? How should modify my measure to achieve that?

Re: How to force one-side of relationship to appear when there is no corresponding record on many side.

rfigtree — Sun, 28 Feb 2021 08:06:03 GMT

You can try adding zero to measure - which turns blank into 0?

....

Return

Result + 0

Re: How to force one-side of relationship to appear when there is no corresponding record on many side.

Anonymous — Sun, 28 Feb 2021 10:26:29 GMT

Brialliant! Thank you very much.

topic How to force one-side of relationship to appear when there is no corresponding record on many side. in DAX Commands and Tips

How to force one-side of relationship to appear when there is no corresponding record on many side.

Re: How to force one-side of relationship to appear when there is no corresponding record on many side.

Re: How to force one-side of relationship to appear when there is no corresponding record on many side.