Using Value from a Filtered Table

[Anonymous]

Another question.

I have a table called t_Fruit with one column named Fruit_Name.  The table contains 3 rows, Apples, Pears and Bananas.

I have added a slicer which is based on t_Fruit[Fruit_Name].

If I display the table t_Fruit as a Table visual and use the slicer I can see that the selected fruit is displayed as the sole occupant of the Table visual.  All good so far.

Elsewhere I am creating a dynamic table using SELECTCOLUMNS.  This new dynamic table is based on the data in another table called t_Shopping.  Basically, I want to create the dynamic table with a column where the value is based on the filtered value.

So,

t_Shopping = SELECTCOLUMNS("Fruit to Buy",Value In Filter)

I thought t_Shopping = SELECTCOLUMNS("Fruit to Buy", MIN(t_Fruit[Fruit_Name])) might work or SELECTEDVALUE(t_Fruit[Fruit_Name]) but neither work.

I am rather confused.

[v-shex-msft]

Hi @Anonymous,

AFAIK, power bi does not support creating a dynamic calculated column/table based on filter/slicer.

Calculate table/column are stored on data model level, measure, filter and slicer are works on data view level(it is a virtual table generated from data model table), you can't use child level to affect its parent.

Regards,

Xiaoxin Sheng

[danno]

The only part of the data model that is created on the fly is within measures.  If you are doing a SELECTCOLUMNS within a calculated table, then that table is only populated when the model is refreshed.  This is not truly dynamic as all the tables within your model have to be refreshed for the calculated table to be refreshed. 

It sounds like maybe you don't have your relationships set up correctly in your model - can you confirm if you have a relationship between your product table and your shopping table? 

[Anonymous]

To help explain what I am trying to do I have created a very simplified version, the link is below.

If you look at the defintion for t_Shopping, I am trying to set the value for the second column in this table to be equal to the type of fruit selected from the filter shown.

Hope this makes it clear.

Thanks.

https://drive.google.com/open?id=1c2NvjYoGi8oG8L5IEBy9KbaQVrBkZHTm

[danno]

Hi, 

still not quite sure why you are doing this, are you trying to analyse fruit sales? 

If so you need to think about a suitable data structure to do that, currently you have 3 separate tables and no relationship between them.  

Power BI is an application for anlaysing data, not creating shopping lists, unless you are perhaps doing basket analysis? 

[amitchandak]

Not very clear. Can you share sample data and sample output.