Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Budfudder
Helper IV
Helper IV

Reference Other Rows In Result Set To Determine Whether To Show A Row

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

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I think you can use ALLEXCEPT (Account ID) to derive the logic.

 

Thanks
Raj

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Budfudder

I'm not clear with your statement, as tested, i think when selecting "no active lease" in a slicer, it would show the rows which "no active leases" is 1. Is my understanding right?

Could you give me examples to understand your requirements?

account leases statuses date   no active leases
A 1 Active 1/1/2017    
A 2 Expired 12/3/2017   1
A 3 Inactive 4/3/2017   1
A 5 Active 1/1/2018    
B 1 Active 3/1/2018    
B 2 Active 1/1/2017    
B 3 Expired 12/3/2017   1
B 4 Inactive 4/3/2017   1
C 1 Active 1/1/2017    
C 2 Expired 12/3/2017   1
D 1 Inactive 4/3/2017   1

 

 

Best Reagrds

Maggie

Anonymous
Not applicable

I think you can use ALLEXCEPT (Account ID) to derive the logic.

 

Thanks
Raj

Sorry, but how? I don't see how it could be used that way.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.