cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GullyBoy Frequent Visitor
Frequent Visitor

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] ) ))
 
 
Capture2.PNGThis is what we are getting now, and INCORRECTCapture3.PNGThis is Ideally should come, but here the Values are Incorrect.
 
Please help. I really appreciate your help.
 
Thanks,
1 ACCEPTED SOLUTION

Accepted Solutions
GullyBoy Frequent Visitor
Frequent Visitor

Re: Visual Filter not woking as expected

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.

 

Capture.PNGCross 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
Community Support Team
Community Support Team

Re: Visual Filter not woking as expected

@GullyBoy ,

 

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.

GullyBoy Frequent Visitor
Frequent Visitor

Re: Visual Filter not woking as expected

@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.

GullyBoy Frequent Visitor
Frequent Visitor

Re: Visual Filter not woking as expected

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.

 

Capture.PNGCross 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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,037 guests
Please welcome our newest community members: