Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

I have the following tables:

HelioD_1-1614484989484.png

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?

 

1 ACCEPTED SOLUTION
rfigtree
Resolver III
Resolver III

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

....

Return 

        Result + 0

View solution in original post

2 REPLIES 2
rfigtree
Resolver III
Resolver III

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

....

Return 

        Result + 0

Anonymous
Not applicable

Brialliant! Thank you very much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors