Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to get non joined items using DAX

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

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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