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
BI-Geniuz
Advocate I
Advocate I

DAX-Dynamic Row Filtering (SELECTEDVALUE/CROSSJOIN)

Hi fellow enthusiasts,

I've stumbled upon a pretty complex situation and was curious if someone has a suggestion to set me on the right track. My customer dropped a "nice-to-have" in my lap and I'm convinced it's doable in DAX but don't want to get lost in the jungle.

So the situation is like this 

powerbi.png

There are assets (rooms) that have attributes. For example, room 748 has no pool and no jacuzzi. Fine.

Desired functionality
When selecting 1 room, they want to see which rooms match with that criteria.

for example:
When selecting room 748, they want to generate a table that shows rooms 652 and 985 (same criteria).

I have been trying to use CROSSJOIN,SELECTEDVALUE,LOOKUPVALUE and also considered to use GENERATE to use all combinations possible. But I don't think I'm on the right track here!

Please help! Thanks!! 

1 ACCEPTED SOLUTION
sreenathv
Solution Sage
Solution Sage

I don't know if this is the optimum solution, but just out of curiosity, I tried to achieve the result and given below is the solution.

 

Assume you have the following table named "Rooms"

 

ROOMPOOLJACUZZISIZE
125YESNOLARGE
124NOYESLARGE
587NONOLARGE
569NOYESSMALL
215YESNOSMALL
325NOYESLARGE
748NONOSMALL
652NONOSMALL
216YESNOSMALL
985NONOSMALL

 

Add a calculated column to this table.

 

 

AttributeCode =
LEFT ( Rooms[POOL], 1 ) & LEFT ( Rooms[JACUZZI], 1 ) & LEFT ( Rooms[SIZE], 1 )

This will give a column with attribute code.

 

 

Now add another calculated table.

 

ROOMS2 = Rooms

Now create a relationship between the 'AttributeCode' field of "Rooms" and "Rooms2"

 

 

 

From Rooms(AttributeCode) To Rooms2(AttributeCode)
Relationship Typ: Many to Many
Cross Filter Direction : Both

Now add the Rooms[Room] field to a slicer.

 

Add the Rooms2[Room], Rooms2[Pool], Rooms2[Jacuzzi], and Rooms2[Size] to a table visual in the report.

 

When you select a Room from Rooms table in the slicer, the AttributeCode of the selected room will be matched against the AttributeCode of Rooms2 table and all the rooms with same AttributeCode will be listed in the table visual based on your slicer selection.

 

Disclaimer: I haven't applied much thought to this solution and this is only a quick solution. So don't mark this as a solution because I am not convinced that this is the optimum solution. 

 

View solution in original post

5 REPLIES 5
sreenathv
Solution Sage
Solution Sage

I don't know if this is the optimum solution, but just out of curiosity, I tried to achieve the result and given below is the solution.

 

Assume you have the following table named "Rooms"

 

ROOMPOOLJACUZZISIZE
125YESNOLARGE
124NOYESLARGE
587NONOLARGE
569NOYESSMALL
215YESNOSMALL
325NOYESLARGE
748NONOSMALL
652NONOSMALL
216YESNOSMALL
985NONOSMALL

 

Add a calculated column to this table.

 

 

AttributeCode =
LEFT ( Rooms[POOL], 1 ) & LEFT ( Rooms[JACUZZI], 1 ) & LEFT ( Rooms[SIZE], 1 )

This will give a column with attribute code.

 

 

Now add another calculated table.

 

ROOMS2 = Rooms

Now create a relationship between the 'AttributeCode' field of "Rooms" and "Rooms2"

 

 

 

From Rooms(AttributeCode) To Rooms2(AttributeCode)
Relationship Typ: Many to Many
Cross Filter Direction : Both

Now add the Rooms[Room] field to a slicer.

 

Add the Rooms2[Room], Rooms2[Pool], Rooms2[Jacuzzi], and Rooms2[Size] to a table visual in the report.

 

When you select a Room from Rooms table in the slicer, the AttributeCode of the selected room will be matched against the AttributeCode of Rooms2 table and all the rooms with same AttributeCode will be listed in the table visual based on your slicer selection.

 

Disclaimer: I haven't applied much thought to this solution and this is only a quick solution. So don't mark this as a solution because I am not convinced that this is the optimum solution. 

 

thank you sir @sreenathv!

This is 100% Exactly what I needed and a perfect solution.

You have provided me the current functionality so I can go on with your solution!

Thanks a lot!!

 

 

Anonymous
Not applicable

Hello @BI-Geniuz ,

 

I personally believe that you should need to put attributes as filters instead of room where you can also filter multiple combinations as per user requirement. For instance, if you will select POOL="Yes", JACUZZI="Yes" and SIZE="SMALL" in filters then ROOM filter accordingly.

 

Thanks,

Bhavankit

Hi @Anonymous,

thanks for your reply. I tried to use every attribute as slicer, but the goal is that when they select a room (as slicer) a table of similar rooms with matching attributes is generate.

Anonymous
Not applicable

Hi @BI-Geniuz ,

 

Could you please share this file here for testing purpose?

 

Thanks,

Bhavankit

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.