I have a table of Accounts and another table of Leases. We don't sell our product; you can lease it for a year at a time. In the Leases table, we record those leases, and each row has a link back to the Account table. So for a given Account, there could be any number of rows in the Leases table. And, of course, for some Accounts there are no rows in the Leases table - they're Accounts that have never leased a product from us.
Leases can have a number of statuses - Active, Expired, Inactive (when the customer surrenders the product early) and so forth.
I have a table visual in Power BI which shows a full list of all Accounts and, for each account, every lease. So there are multiple rows for some Accounts, with a row for each lease associated with the Account. I have slicers so that users can choose to display only Active leases, or only Expired leases, and so forth. So if, for a particular account, there exists this year's lease (Active) and last year's lease (Expired), that Account would show if I chose the 'Active' slicer option or the 'Expired' slicer option, each time just showing the lease that meets the slicer criteria.
That's all good. But now the user has said they want to have a slicer that will show them all Accounts which have no leases, and another slicer that will show them all Accounts which have no Active leases. And that's where I encounter problems.
The one that shows all Accounts which have no leases is easy - there'll only ever be one row for such an Account, so I can just filter on whether or not the lease ID for a row is null.
But how can I do the slicer for Accounts which have no Active leases? I can say 'show me rows where there is no lease or the lease is not Active' - but all that will do is knock out of the result set any current leases - it will still show Accounts that have current leases as long as they also have some expired leases (it will just show the rows for them containing those expired leases, not the rows containing the current leases). That leads to the title of the thread. Is there some way to look at other rows, and decided to show it or not based on whether other rows contain some value? Something like:
FOR (each row in the result set)
IF (any other row in the result set has the same Account ID as this row and contains a current lease)
Do not show this row
ELSE
Show this row
END IF
END FOR
