Add New Column that query data from another table based on Slicer selection

[Anonymous]

Hi All,

Appreciate if you guys can help.

I have two tables:

1. Schedule Table

2. Profile Table

I would like to add additional column in my schedule table, based on the selection of the profile, to query the latest part no from my profile table for each model no and output the part no to the new column.

Schedule table with profile slicer:

Profile table:

this can be done in sql with join statement, but i want to integrate with slicer selection to output the part no. Please advise. Thank you.

Regards

[Ashish_Mathur]

Hi,

Please show a few rows of both tables and also show the expected result.

[Anonymous]

Hi @Ashish_Mathur ,

Thank you for the reply. Please find the following details:

Schedule Table:

Profile Table:

I have schedule table which show the production schedule of model and profile table which store the part no for each model. Because each model have a few part no and each part no production line only require to see relevant part no. Hence, based on user selection of profile with the slicer, I want to show only the selected part no to the user.

Example:

When I choose 'Carton Box' from my slicer, it should query my profile table for the part no for each model no from my schedule table and add new column of part no for carton box only into my schedule table.

Hope it clarifies. Thank you.

Regards.

Jenas

[Ashish_Mathur]

Hi,

I am still not clear.  Please share small dummy datasets (which are easy to read and understand) and on that, please show the expected result.  Also, share the link from where i can download your PBI file.

[Anonymous]

Hi @Ashish_Mathur ,

Please refer to the attached pbi file. The dateset had been imported to the report. 

https://drive.google.com/file/d/1W3jj0nzmyL24LySgyHvMFHuSHJpXnvsf/view?usp=sharing

For the expected result, I wanted to add a new column to my schedule table with profile selected. The column shoud be the part no of the selected profile for respective model at each line. 

I hope below pictures can explain what  I wanted. Thank you for your time.

[Ashish_Mathur]

Hi,

In the Schedule Table, write this calculated column formula

=Column = LOOKUPVALUE(Profile[PartNo],Profile[id],Schedule[ID])

Hope this helps.

[Anonymous]

Hi All,

Anyone can assist on this? Appreciate it!

Regards.

Jenas

[Pragati11]

Hi @Anonymous ,

Is there a common column in both of your tables? If yes trying creating a relationship between these 2 tables.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

Yes I have created the relationship between this two tables. But the profile table contains duplicate records, so I would need to filter out the latest profile for that model no.

With the slicer involved, do you know how to construct the dax? Please advise. Thank you.

Regards.

Jenas