Use a variable in filter as operand

[BoNDoK]

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.

[v-diye-msft · Accepted solution]

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.

[BoNDoK]

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

[Nathaniel_C]

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.
Nathaniel

[BoNDoK]

Hi 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

[parry2k]

@BoNDoK you cannot create a calculate table using slicers. if you explain what you are trying to achieve and why you need this calculated table

[BoNDoK]

Hi Parry,

Here is what I need to achieve:

I have a sales table, with a hierarchy (region, country, product category, product subcategory) and date dimension.  The user would like to see the numbers on a line chart, with the hierarchy as a slicer, and date on x-axis and the trick is: when any item of the slicer is selected: the chart legend should show the children of the node selected, and if nothing is selected it should show the first level nodes (regions).

My approach is:

1. Transpose the fact table so each of the 4 levels is on a separate line. 

2. Use the transposed table as the legend.  But extra filtering is needed and that's why I would like to filter based on the selected member of a slicer.

Thank you,
BoNDoK