Forum Discussion
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
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!!
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"
ROOM POOL JACUZZI SIZE 125 YES NO LARGE 124 NO YES LARGE 587 NO NO LARGE 569 NO YES SMALL 215 YES NO SMALL 325 NO YES LARGE 748 NO NO SMALL 652 NO NO SMALL 216 YES NO SMALL 985 NO NO SMALL 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.
5 Replies
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"
ROOM POOL JACUZZI SIZE 125 YES NO LARGE 124 NO YES LARGE 587 NO NO LARGE 569 NO YES SMALL 215 YES NO SMALL 325 NO YES LARGE 748 NO NO SMALL 652 NO NO SMALL 216 YES NO SMALL 985 NO NO SMALL 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 Anonymous!
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!!
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
