How to get non joined items using dax

Question

I am working on Power BI dax problem. I have Invoice table(M) joined with dealers table(1). Invoice table is joined to another few tables like Distributors,items etc. So I can slice invoice data. Since dealer table joined to invoice table I always get dealers with invoice values. How do I get "non invoiced" dealers using Dax statement. I need to get non invoiced dealers but it need to slice by Country, Region wise. equavalent sql query should be like this.

select a.* from a left outer join b on a.id = b.a_id where b.a_id is null

Here is what I tried , but I think values are not correct.

TotalDealers = CALCULATE(COUNTROWS('dealers'),ALL('dealers'))

Thank you!

That schema doesn't show how Dealers are joined to Regions / Countries? — Olly
Based on that schema, you will never be able to slice uninvoiced dealers by geography. There's no way to determine the region of a dealer with no invoice. — Olly

Olly Olly · Accepted Answer · 2018-12-14T16:59:39

If you want a count of dealers without invoices, you can use something like this:

Uninvoiced Dealers = 
COUNTROWS ( 
    FILTER ( 
        tbl_dealers,
        COUNTROWS ( RELATEDTABLE( tbl_invoice ) ) = 0
    )
)

However, your schema doesn't allow you to slice uninvoiced dealers by region, as there is no relationship to determine the region of dealers with no invoice...

If the region of uninvoiced dealers is determined via the salesrep table, then you need to add an (inactive) relationship between salesreps and regions, and use USERELATIONSHIP as a filter in your measure:

Uninvoiced Dealers = 
CALCULATE ( 
    COUNTROWS ( 
        FILTER ( 
            tbl_dealers,
            COUNTROWS ( RELATEDTABLE( tbl_invoice ) ) = 0
        )
    ),
    USERELATIONSHIP ( tbl_salesreps[region_id], tbl_regions[id] )
)

How to get non joined items using dax

1 Answers