Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Fairly new to Power BI so forgive me if I am overlooking something obvious.
I have a set of data that is essentially bits of equipment out on loan to clients, when we go out to repair a bit of equipment its useful for our technicians to know what else the client has, so I want to be able to use the text slicer to search for an equipment number but have the table visual return all pieces of equipment that client current has on loan, not just the specific piece of equipment that was searched on in the text slicer.
(for reasons I won't get into it was decided the technician cannot search on client details directly)
Does that make sense?
Solved! Go to Solution.
Hi @Gazza21,
I'm afraid the Search visual doesn't help in your scenario. Because it will filter out the unmatched records. Please download the demo in the attachment, which is the solution for your scenario.
1. Create an independent table of Assets.
Assets = FILTER ( VALUES ( Table1[Asset] ), ISBLANK ( [Asset] ) = FALSE () )
2. Create a measure.
Measure = VAR clients = CALCULATETABLE ( VALUES ( Table1[ClientNumber] ), ALL ( Table1[AD1], Table1[AD2], Table1[Asset] ) ) RETURN CALCULATE ( IF ( MIN ( Table1[ClientNumber] ) IN clients, 1, BLANK () ), ALLEXCEPT ( Table1, Table1[ClientNumber] ) )
3. Create a Table visual and a Slicer.
Best Regards,
Dale
Hi @Gazza21
Please share some sample data or your PIBX file.
As you have mentioned that you need to be able to filter based on the bits of equipment, so one possible issue can be that the same bit ID can be available at multiple customers. Or the bit ids which are being searched are always unique?
Regards,
Affan
The equipment number is unique (its called Asset number in the image below).
hopefully this screenshot helps:
Thanks, exactly what I needed
Hi @Gazza21,
I'm afraid the Search visual doesn't help in your scenario. Because it will filter out the unmatched records. Please download the demo in the attachment, which is the solution for your scenario.
1. Create an independent table of Assets.
Assets = FILTER ( VALUES ( Table1[Asset] ), ISBLANK ( [Asset] ) = FALSE () )
2. Create a measure.
Measure = VAR clients = CALCULATETABLE ( VALUES ( Table1[ClientNumber] ), ALL ( Table1[AD1], Table1[AD2], Table1[Asset] ) ) RETURN CALCULATE ( IF ( MIN ( Table1[ClientNumber] ) IN clients, 1, BLANK () ), ALLEXCEPT ( Table1, Table1[ClientNumber] ) )
3. Create a Table visual and a Slicer.
Best Regards,
Dale
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |