Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Visual Filter not woking as expected

Hi All,

 

I am new to PowerBI and I am trying to achieve a particular logic with multiple charts. I'm in the half way but stuck in one position.

Here's the problem.

 

I've two bar charts. Each of them having 2 levels of details on X-axis. I'm not using a drilldown feature, they are showing in a single level hierarchy. When I am clicking on one of the bar charts, the other chart should highlight accordingly. both charts using the % of total as value field with different slicers. so, ideally if I filter on one bar in the first chart, the second chart should highlight the single bar. 

 

Here I have used one more logic which is in DAXquery to calculate the % of total. Per requirement, we shouldnt use the %GT (% of Grand Total) feature in the value field. We have to use the % of group total of one of the x-axis value. So, I used a measure to calculate the same, by grouping the axis value.

 

Now, When I click on the bar chart, it highlights all the bars in the second chart. its wrong ideally. And if I remove the grouping fucntionality in the DAX query, it works well. but the values will not be as expected.

 

Here's the DAX query I have used for getting % of group total for each chart.

 

ByHTypeValue = DIVIDE(CALCULATE(sum('VW_MODEL'[TotalValue]),
        ALLSELECTED('VW_MODEL'[Xaxis1])),  
        CALCULATE(sum('VW_MODEL'[TotalValue]),
            ALLSELECTED('VW_MODEL'[Xaxis2),
            ALLSELECTED('VW_MODEL'[Xaxis1] ),
            ALLSELECTED('VW_MODEL'[HType] ) ))
 
ByMTypeValue = DIVIDE(CALCULATE(sum('VW_MODEL'[TotalValue]),
        ALLSELECTED('VW_MODEL'[Xaxis1])),  
        CALCULATE(sum('VW_MODEL'[TotalValue]),
            ALLSELECTED('VW_MODEL'[Xaxis2),
            ALLSELECTED('VW_MODEL'[Xaxis1] ),
            ALLSELECTED('VW_MODEL'[MType] ) ))
 
 
This is what we are getting now, and INCORRECTThis is what we are getting now, and INCORRECTThis is Ideally should come, but here the Values are Incorrect.This is Ideally should come, but here the Values are Incorrect.
 
Please help. I really appreciate your help.
 
Thanks,
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Everyone, 

 

I am able to resolve this issue, by using Cross Filtering feature available in Feb 2019 PowerBI desktop release. Using this feature you can choose either you want to Cross highlight or Cross Filter your charts based on the selection. This option will available when you click on the "Edit Interactions" button under the Format ribbon just like the below.

 

Cross Highlight-Cross Filter IconsCross Highlight-Cross Filter Icons

 

By default, it would be Cross Highlighting, you can change it to Cross filtering by clicking the above "chart and filter" icon, then the chart will get filtered based on the other selection.

 

For me it worked. In my chart it was highlighting 4 bars instead of 1 because of a grouping query. That gets filtered out and only one bar which is expected, popping up based on the selection.

 

Anyway, thanks everyone for your time and effort. Hope I explained it clearly so that it would help others in the similar situation.

 

Thanks again!

 

 

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

Modify measures like patttern below and check if they can work:

ByHTypeValue =
DIVIDE (
    CALCULATE ( SUM ( 'VW_MODEL'[TotalValue] ), ALLSELECTED ( 'VW_MODEL'[Xaxis1] ) ),
    CALCULATE (
        SUM ( 'VW_MODEL'[TotalValue] ),
        ALLSELECTED ( 'VW_MODEL'[Xaxis2)) +  CALCULATE(sum('VW_MODEL'[TotalValue] ),
        ALLSELECTED ( 'VW_MODEL'[Xaxis1] )
    )
        + CALCULATE ( SUM ( 'VW_MODEL'[TotalValue] ), ALLSELECTED ( 'VW_MODEL'[HType] ) )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@v-yuta-msft 

 

Thanks for looking into. I really appreciate it. But that didnt really help. 

 

I'm facing this issue because I used a logic to get the group total based on Xaxis1. Due to that when I click on any part of the bar chart, the below chart incorrectly highlights. 

 

Either, I have to use a different DAX query logic to get the group total. (i.e., the sum of Xaxis1 group to 100%. It means all 1's summed to 100%, all 2's summed to 100% and so on.)

 

Or, I need to edit the code which triggers while clicking on the chart (I dont even know that's possible or not) to choose the partcular bar based on Xaxis1 and Xaxis2.

 

Hope you understood the issue better. Any help would be appreciated.

Anonymous
Not applicable

Hi Everyone, 

 

I am able to resolve this issue, by using Cross Filtering feature available in Feb 2019 PowerBI desktop release. Using this feature you can choose either you want to Cross highlight or Cross Filter your charts based on the selection. This option will available when you click on the "Edit Interactions" button under the Format ribbon just like the below.

 

Cross Highlight-Cross Filter IconsCross Highlight-Cross Filter Icons

 

By default, it would be Cross Highlighting, you can change it to Cross filtering by clicking the above "chart and filter" icon, then the chart will get filtered based on the other selection.

 

For me it worked. In my chart it was highlighting 4 bars instead of 1 because of a grouping query. That gets filtered out and only one bar which is expected, popping up based on the selection.

 

Anyway, thanks everyone for your time and effort. Hope I explained it clearly so that it would help others in the similar situation.

 

Thanks again!

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.