Writing a measure based off of parameters (should be simple)

[Anonymous]

Hello, I have some data represented below

I am looking to write a measure that returns a number (1-5 from the test column) based on the rules seen in the # Customers column. I want the number to appear on a card when one of the locations are selected. Thanks!

[v-xiaotang]

Hi @Anonymous 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate!
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,
Community Support Team _Tang

[v-xiaotang · Accepted solution]

Hi @Anonymous 

try this

-

1. split column

2. create the measure

Tests Required = 
var _customers=SELECTEDVALUE(Location[Customers])
return
CALCULATE(MIN('Table'[# Tests Required]),FILTER('Table','Table'[Start]<=_customers&&'Table'[End]>=_customers))

result

Best Regards,

Community Support Team _Tang

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

[Greg_Deckler]

@Anonymous Split your column in your first table into two columns using "-" as your delimiter. Then the issue is trivial.

[Anonymous]

This isn't how my data looks in my report. It's actually noot even included, I was hoping to just manually type in the numbers in the measure. Should I add a table and do it that way?

[Greg_Deckler]

@Anonymous You could, or if you want it in a measure hard coded you can do this:

Tests Required Measure = 
  VAR __Customers = SUM('Table'[Customers])
RETURN
  SWITCH(TRUE(),
    __Customers > 0 && __Customers < 201,1,
    __Customers > 200 && __Customers < 501,1,
    __Customers > 500 && __Customers < 1001,1,
    __Customers > 1000 && __Customers < 2001,1,
    __Customers > 2000 && __Customers < 2501,1,
  )