How to get non joined items using DAX

[Anonymous]

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 salesrep, 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

[Anonymous · Accepted solution]

@Anonymous,

Create a calcualted column in dealers table.

CheckColumn = COUNTROWS(RELATEDTABLE(invoice))

Dragging all fields of dealers table into a table visual, then set the value of CheckColumn to "is blank" in visual level filter of the table visual.


Regards,
Lydia