synchronize the slicer from one table to another table

[Anonymous]

Hello All, 

i have 2 tables 

1) Substation 

Year	State	Substation	Substation#	System	Project phase
2020	AZ	Wells 49	332549	Distribution Line	MODEL
2020	AZ	Heavener 22	351422	Distribution Line	MODEL
2020	AZ	Otter 41	435341	Distribution Line	MODEL
2020	AZ	Lone Grove 23	512523	Distribution Line	MODEL
2020	AZ	Wells 49	332549	Distribution Line	PLAN
2020	AZ	Heavener 22	351422	Distribution Substation	PLAN
2020	AZ	Otter 41	435341	Distribution Line	PLAN
2020	AZ	Lone Grove 23	512523	Distribution Substation	PLAN

2) Labor

Year	State	Substation	Substation#	System	Labor Type	Project Phase
2020	AZ	Wells 49	332549	Distribution Line	Design	MODEL
2020	AZ	Heavener 22	351422	Distribution Line	Execution	MODEL
2020	AZ	Otter 41	435341	Distribution Line	Design	MODEL
2020	AZ	Lone Grove 23	512523	Distribution Line	Execution	MODEL
2020	AZ	Wells 49	332549	Distribution Line	Engineering	PLAN
2020	AZ	Heavener 22	351422	Distribution Substation	Construction	PLAN
2020	AZ	Otter 41	435341	Distribution Line	Engineering	PLAN
2020	AZ	Lone Grove 23	512523	Distribution Substation	Construction	PLAN

i am joining my 2 tables with Substation number (Substation table filters the Labor table)
when i keep the Project Phase as the slicer, i am not getting the results as excepted
Example
my slicer Project Phase from table Substation

when i select PLAN in the slicer Project Phase, the Labor table is not filtering to the Plan (showing both the Plan and Model data)

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

As a workaround, you can try to create a inactive relationship as below:

Use the function "userelationship" to create a measure:

M_Project Phase = CALCULATE(MAX(Labor[Project Phase]), USERELATIONSHIP(Substation[Project phase],Labor[Project Phase]))

 Then create  a visual control measure and applied  it to the visual filter pane.

visual control = IF([M_Project Phase]=SELECTEDVALUE(Labor[Project Phase]),1,0)

The result will show as below:

Please check the attached demo for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi, @Anonymous 

Since you are establishing a relationship based on filed "Substation#",so the table is  filtered through this filed "Substation#" rather than filed "Project phase".

For example:
When you select"Plan" in slicer Project Phase from table Substation,you will filter the table “Labor”as below:

You get the  value list of "Substation#" : "332549","351422","435341","512523" rather than  value list of "Project Phrase":"Plan".

Then in table “Substation”, it will filter out all the records that contain the same value as the value list "Substation#" : "332549","351422","435341","512523".

Best Regards,
Community Support Team _ Eason

[TomMartens]

Hey @Anonymous ,

consider to create a dedicated table that can be used to relate both your existing tables, then this table can be used to filter both tables at once.

You can create a table using this DAX statement

dim table Project Phase =
DISTINCT(
UNION(
ALLNOBLANKROWS('Substation'[Project Phase])
, ALLNOBLANKROWS('Labor'[Project Phase])
)
)

It's always a good idea to create a data model that is based on the star schema modeling concept.

This article describes how to create a table that is based on DAX: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-tables

This explains how to create a data model in Power BI: https://docs.microsoft.com/en-us/learn/paths/model-power-bi/

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[Anonymous]

Thanks for the Approach
this doesn’t solve anything for me
i created the table and still its the same

[TomMartens]

Hey @Anonymous ,

please create a pbix file that contains sample data, and reflects your data data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom