Filter X-axis of Bar Chart through another Bar Chart

[Anonymous]

Hi Power BI users, this is my first post here and really looking for some help.

I have a situation (See attached image) where I have 2 bar charts and when I select a particular bar on Chart A (which has info on Temp. corresponding to Lot.No.), I want to filter Chart B's X-axis to show all Lot.No. corresponding to the grade for the Lot. No. selected in Chart A.

(e.g. If I select Lot.No.10 in Chart A (which falls under Grade B), I want Chart B to filter showing Lot.Nos 6 to 10. Ideally, I would like if in addition, Lot.No. 10 is highlighted among these Lots in some fashion too).

For reference, I have included the data set as table on the left.

Please let me know if there is some way I can achieve this.

Thanks in advance!

Regards,

Akki

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may copy the data table and create measures to get the chart B.

Measure = var a= CALCULATE(SELECTEDVALUE(Data[Grade]),ALLSELECTED(Data)) return IF(ISFILTERED(Data[Lot.No]),IF(MAX('Data (2)'[Grade])=a,1),1)

Then use conditional formatting for chart B.Attached sample file for your reference.

Rule = IF(SELECTEDVALUE('Data (2)'[Lot.No])=SELECTEDVALUE(Data[Lot.No]),1)

Regards,

[Anonymous]

Hi @v-cherch-msft , thank you for this brilliant solution!

I can see it works as desired, but I am not able to properly understand how the measures created are working. Could you please help me understand that as well.

Also, the actual sheet I will be working with will have data added to it constantly. In this case I think copying table might not work properly and I might need to refernce it. I tried creating extra sheets referncing as well duplicating the original sheet but nothing seems to work like you did. Maybe it's becuause I have not understood how this works.

I wish I was allowed to attach pbix files here.

[Anonymous]

Hi @v-cherch-msft , I checked again. This method works only when I manually copy-paste the table but not in cases where I duplicate or reference the table.

Is there any way where I can perform such a task using the same table? Or at least using a reference where I don't have to manually repeat the copy-paste procedure every time data is updated (every hour).

[v-cherch-msft]

Hi @Anonymous 

You may use edit interactions as below.You can duplicate table in query editor.

Regards,

[Anonymous]

@v-cherch-msft  Sorry, I was not changing variables in the visual filter. Method you suggested works well for reference also. Thanks a ton!

I have an additional request. What if my chart B is a line chart and has both temp. and pressure on y-axis with Lot.No. on X-axis. I want to similarly screen the grades when I select a Lot in Chart A, but as I want to highlight the particular Lot. No. selected, I would like to have a vertical line on that lot in Chart B. I tried using the line and stacked column chart putting the measure  "Reference Line = SELECTEDVALUE(Data[Lot. No.])"  as column but I get a clumsy graph in that case. Is there any way I get a  dynamic vertical line  instead?