Forum Discussion
Use a variable in filter as operand
I have the following tables in Power BI:
FactData
Filtering Table
I need to create another table programmatically (using DAX) to filter the Fact Table using the selected item from the filtering table. Here is what I have done:
1. Insert a Slicer with the "Filtering Table" data.
2. Create the filtered table using this DAX statement:
filteredFact =
filter(
all(FactData),
[Cola]=SELECTEDVALUE(filteringTable[Col1])
)3. Created a measure to make sure the selectedValue is returning data:
selectedValue = SELECTEDVALUE(filteringTable[Col1])Expected Result
I expect to get back in FilteredTable only the rows selected from the filter containing FilteringTable[Col1]
Actual Result
I always get an empty table.
Hi BoNDoK
Let me know if you'd like to get below results:
1. DONOT manage the relationship between them
2. Add a measure and drag it to the filter pane in FactData table visual, set it as 1
Measure = IF(SELECTEDVALUE('Filtering Table'[Col1])=MAX(FactData[Cola]),1)3. final results:
Pbix attached.
6 Replies
Hi BoNDoK
Let me know if you'd like to get below results:
1. DONOT manage the relationship between them
2. Add a measure and drag it to the filter pane in FactData table visual, set it as 1
Measure = IF(SELECTEDVALUE('Filtering Table'[Col1])=MAX(FactData[Cola]),1)3. final results:
Pbix attached.
Hi Dina Ye,
Thanks a lot!!! This is exactly what I wanted to achieve. But can you please explain to me how this visual level filter is doing this magic? I see it working but I don't understand what is happening behind the scene.
Regards,
BoNDoK
Hi BoNDoK ,
Not sure what you are trying to do, but with the two tables as a starting point, and then create a relationship, I can get to the selected outcome.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielHi Nathaniel,
Thank you for your response.
I cannot add this relationship due to other needed relationships in my model. I have a requirement from a user, to show custom legends on a line chart. And to achieve it, I need to be able to establish the connection I described without using Power BI relationships.
Regards,
BoNDoK
