cancel
Showing results for 
Search instead for 
Did you mean: 
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

View solution in original post

Anonymous
Not applicable

Brialliant! Thank you very much.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors