Table not displaying proper relationship between vendor and item

[Anonymous]

Hello,

I am trying to display a Days of Supply key measure in a table by Warehouse, Vendor, and Item. Whenever I drop in the 'Vendor Master'[Vendor Name] or 'Item Master'[Item Number & Description] fields the table no longer populates correctly. For example, pictured below you can clearly see that Bimbo items are showing next to the ARIZONA TEA DD vendor. I've been reviewing the table relationships and cannot find a reason for why this isn't working. Attached is a link to the file.

https://1drv.ms/u/s!AiFttrDOn_FqgRgcKQFRda9qfDBg?e=1NzyHU

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

It's because of the IF amount = 0, 0 checks in your measures and the way Tabular models filter out table combinations.  There is no relationship directly between say 'Warehouse Master' and 'Vendor Master' and when you put fields from both of those tables into a visual, the way PowerBI decides what combinations to display is by filtering out rows where the measures return BLANK.

By adding in the IF amount = 0, 0  the measure doesn't return a BLANK so no combination is excluded.  It performs a CROSSJOIN on both tables returning every possible combination and giving you a 0 on all the combinations that don't appear in your 'Stored Item' table or 'Order Detail' table.

I added a measure to count all the rows that exists in both the 'stored items' and 'orders detail' looking at all the lookup tables and returning a 1 for all the valid combinations.

LiveRowCheck = 
VAR OrderRowCheck =
    COUNTROWS (
        SUMMARIZE (
            'Order Detail',
            'Item Vendor Master'[Wh-Item ID],
            'Vendor Master'[Vendor ID],
            'Warehouse Master'[Warehouse ID],
            'Item Master'[Wh-Item ID],
            'Order Master'[Order ID]
        )
    )
VAR StoredRowCheck =
    COUNTROWS (
        SUMMARIZE (
            'Stored Item',
            'Warehouse Master'[Warehouse ID],
            'Vendor Master'[Vendor ID],
            'Item Master'[Wh-Item ID]
        )
    )
RETURN
    IF ( NOT ISBLANK ( OrderRowCheck + StoredRowCheck ), 1 )

We can combine that with the IF = 0 check to return 0 for only BLANK but valid rows.

Avg Daily Usage = 
Var Amount = DIVIDE( [Total Store Qty Ordered Last 31 Days],[Distinct Count of Days Last 31 Days], 0 )
RETURN IF ( AND ( ISBLANK ( Amount ), [LiveRowCheck] = 1 ), 0, Amount )

I updated the measures in the model and uploaded a copy here for you to look at.  Days of Supply Table Issue updated.pbix

[Anonymous]

@jdbuchanan71  Wow this is very informative. Thank you! This worked and I was also able to get the results I needed by removing the IF amount = 0 , 0 logic from my Current On Hand and Days of Supply key measures.