Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Visual filter not working when using calculate formula

I'm trying to create an average of work completed based on the number of cases worked, what task they worked, and the number of days.

 

I created the left visual which is a simple sum of case / count of days.  But in this calculation I need to exclude a specific task called "N/A"

 

I created the right visual which is the sum of cases where the task is not N/A / count of days.  When I select a task from the visual in the top left it doesn't correctly filter the right visual, but it does filter the left visual.

 

I'm new to power bi so i might be overlooking something simple.  Any idea why this is happening?

 

Formula for left visual

Average Daily Cases = DIVIDE(SUM(Append1[Cases]),DISTINCTCOUNT(Append1[Date]))

 

Formula for right visual

Average Daily Cases2 = DIVIDE(CALCULATE(SUM(Append1[Cases]),'Append1'[Task] <> "N/A"),DISTINCTCOUNT(Append1[Date]))
 
Need Help

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Your measures are straight forward.  Just to confirm - you've checked in Format/Edit Interactions that the visual is set to crossfilter the visual on the right?

    Regards,

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      mahoneypat v-yingjl Yes I've verified that both visuals are set to the Highlight option for filtering.  I've tried creating the Right visual from scratch and copying the left visual and replacing the measures but neither option worked.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I'll also add that other visual filtering does work.  For some reason when I try to visual filter with that left left visual it's not working as expected.

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Please check whether you have set some other filters on the right visual or set the top left visual cross-filtering status as None to the right visual.

     

    Best Regards,
    Yingjie Li

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