Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

DAX

Hello need help with a formula.

I am trying to create a dax formula based off the below data.

The formula should say

if the Sum/Count of the load number in Jan is

less than 3 = "75.00"

greater than or equal to 3 = "100.00"

greater than or equal to 4 = "125.00"

greater than 6 = "225.00"

other wise Null.

So Jan should give me a sum of 4 Loads and a fee of 125.00

Feb would give me a sum of 2 loads and a fee of 75.00 etc.

try:

Measure = 
VAR _Count=COUNTROWS('Table')
RETURN
SWITCH(TRUE(),
_Count > 9, 300,
_Count >= 6, 200,
_Count >= 3 ,100,
_Count = 2, 75,
50)

7 Replies

ryan_mayu
Super User
4 years ago
Anonymous

pls try this

Measure = VAR _count=COUNTROWS('Table') return SWITCH(TRUE(),_count<3,75,_count=3,100,_count=4,125,_count>6,225)
- TheoC
  Community Champion
  4 years ago
  ryan_mayu, I did have a solution that has now been deleted given the simplicity of yours 😂!!
  
  Love your work mate!
  - ryan_mayu
    Super User
    4 years ago
    Thank you
- Anonymous
  Not applicable
  4 years ago
  Hello this is perfect How could I add a between in the formula ? for volume that is between 3 to 5 would equal 300
  - ryan_mayu
    Super User
    4 years ago
    Anonymous
    
    this logic is in conflict with your first one which is equal to 4 = "125.00"
    
    pls clarity the whole logics or you can try to modify the measure like below.
    
    Measure = VAR _count=COUNTROWS('Table') return SWITCH(TRUE(),_count<3,75,_count=3,100,_count<5,300,_count>6,225)

Related content