Forum Discussion

Annihilo's avatar
Annihilo
Regular Visitor
4 years ago
Solved

cross-visual filter issues

Dear Community,

I'm having an issue with visual filtering affecting a measure in an unexpected way.

Background:
There is a group of agents who do various types of services. I want to show the average time the whole group spends on a certain activity vs. selected individual agent. In Visual #1 you can select the agent and in visual #2 you would see the various activities with average time vs the individual agent's average time. It works as expected so far.

Problem:
When I change interaction of Visual #1 to Visual #2 to 'filter' from 'highlight' the group averages become messed up. (This would be necessary as there are always activities which a certain individual does not perform so they are redundant on Visual #2 above (the bar sections where there is no black bar in the above exhibit).
This is what happens if I change the interaction:

(the red bars are no longer showing the group average times, but of the selected agent instead)


This is the measure of the group average time.

I'm pretty sure that's what I need to update to fix the issue:

Average time_noowner = [Worktime_noowner]/[Acts_noowner]

where
Worktime_noowner = calculate(sumx(Source,Source[Duration_]),removefilters('Source'[Owner]))
Acts_noowner = calculate(COUNT(Source[Market]),REMOVEFILTERS('Source'[Owner]))

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi  Annihilo, 

     

    You need create a new table via code.

    Table = VALUES(Data[Name])
     
    And replace the [Name] from 'Data' with 'Table'[Name] in first table visual.
    Then change Avg_ALL code as the following.
    Avg_ALL =
    IF( [AverageDuration] = BLANK(), BLANK(),
    CALCULATE( [AverageDuration], ALL( 'Table'[Name] ) ) )

    Result: 

     

     

    Your pbix file in the end.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

3 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Annihilo ,

     

    A little less able to understand your question.From 'highlight' to 'filter'. as i know form your measure, the result wont change even if you click the owner. Because it has been removed filter on owner. But the black bar result maybe wrong.

    Or you can try allexcept() to retrun a table with all filters removed except for the filters on Market column.

    if you want more help please share some example data and expect result.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

     

     

     

    • Annihilo's avatar
      Annihilo
      Regular Visitor
      4 years ago

      I use 'highlight' for the bar chart interaction. The 'average all' measure shows 123.61 minutes for the whole group. This is what I'd like to see retained if I switch the interaction to 'filter'.

      But there it becomes 123.28. I don't understand the reason.

      Link for the source pbix file:
      Source 

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi  Annihilo, 

         

        You need create a new table via code.

        Table = VALUES(Data[Name])
         
        And replace the [Name] from 'Data' with 'Table'[Name] in first table visual.
        Then change Avg_ALL code as the following.
        Avg_ALL =
        IF( [AverageDuration] = BLANK(), BLANK(),
        CALCULATE( [AverageDuration], ALL( 'Table'[Name] ) ) )

        Result: 

         

         

        Your pbix file in the end.

         

        Best Regards

        Community Support Team _ chenwu zhu

         

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