Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filtering on measure that excludes values

Hello,   my Booking Rate is calculated as follows: Bookings divided by Sales, where Sales from USA or Canada should not be taken into account. I translated it into DAX as follows: Booking Rate = ...
Need Help
  • Wilson_'s avatar
    Wilson_
    2 years ago

    Natalie,

     

    As I mentioned, filtering out Canada and USA by using FILTER will respect the filter context of the visual when it calculates the Booking Rate for you. However, when you use CALCULATE here, the calculate modifier is on the same column of the table that you're using in the visual and therefore it overwrites the filter coming from the table.

     

    Let me know if that doesn't make sense. 😄


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Wilson_'s avatar
Wilson_
Icon for Memorable Member rankMemorable Member
2 years ago

Hey Natalie,

 

Does it work for you if you change the Sales without USA Canada measure to the below? Filtering out Canada and USA by using FILTER will respect the filter context of the visual when it calculates the Booking Rate for you. 

 

 

Sales without USA Canada = 
VAR NonNACountries =
FILTER (
    VALUES ( dimoutlet[CountryName] ),
    dimoutlet[CountryName] <> "USA" && dimoutlet[CountryName] <> "Canada"
)

RETURN
CALCULATE (
    [Sales], 
    NonNACountries
)

 

 

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    This seems to work great!! Could you explain me what the difference is to this measure?

    Sales without USA Canada = CALCULATE([Sales], NOT(dimoutlet[CountryName] = "USA" || dimoutlet[CountryName] = "Canada"))

    • Wilson_'s avatar
      Wilson_
      Icon for Memorable Member rankMemorable Member
      2 years ago

      Natalie,

       

      As I mentioned, filtering out Canada and USA by using FILTER will respect the filter context of the visual when it calculates the Booking Rate for you. However, when you use CALCULATE here, the calculate modifier is on the same column of the table that you're using in the visual and therefore it overwrites the filter coming from the table.

       

      Let me know if that doesn't make sense. 😄


      ----------------------------------
      If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)