Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Creating one Dax formula for multiple IF statements

 

@ links to members, content
 
Hello I need assistance in getting this IF statement to work in Dax formula.  I am trying to group my data based on gift amounts.  I have tried the grouping functionality in Power BI but it does not achieve what I am wanting to do.  I would like these if statements to be one DAX formula as I would like to also use the DAX as a filter.
 

if hmg_gift_amoount between 10.00 and 99.99 and hmg_gift_cycle = Recurring then " Recurring 10 - 99.99"
Else If hmg_gift_amoount between 100.00 and 249.99 and hmg_gift_cycle = Recurring then " Recurring 100 - 249.99"
Else "Recurring 250+"

if hmg_gift_amoount between 10.00 and 499.99 and hmg_gift_cycle = Single then " Single 10 - 499.99"
Else If hmg_gift_amoount between 500.00 and 999.99 and hmg_gift_cycle = Single then " Single 500 - 999.99"
Else "Single 1000+"

 

Any help would be greatly appreciated! 

Need Help
Show and Tell
Tips and Tricks

7 Replies

  • Waqas_BIspecs's avatar
    Waqas_BIspecs
    Frequent Visitor
    3 years ago

    Hello, there are two ways to do this, either you create a calculated column with these bins of create a conditional column in power query. Both will work perfect.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I have tried that, I cannot get it to work.  Please share an example here. 

  • Waqas_BIspecs's avatar
    Waqas_BIspecs
    Frequent Visitor
    3 years ago

    for  your convenience, i am putting this image as an Example. you can use your data to build this. 

     

    I hope this works for you. Please do give your feed back for the solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      As stated earlier, this does not work properly for what I am trying to do.  That is why I want to do this with a DAX formula.  I have tried this. 

  • Waqas_BIspecs's avatar
    Waqas_BIspecs
    Frequent Visitor
    3 years ago

    OK before i post the example, the potential problem i see here that bin values are overlapping which is making it difficult for you to handle. 
    for reccuring 1st bin is between 10  - 49.99 while for single the bin is 10 - 499.99. We can not handle this the way you are trying to handle in one statement. if you can somehow manage a unique bin size then below will definitely help. 
    Step1: Create a conditional columns to define the bins 
    Step2: You can create a conditional column to compare these with Reccuring / Single


    Step2:
    Create a Conditional Column for comparison

    Comparison = if(Sheet1[Brackets] = "A" && 'Sheet1'[hmg_gift_cycle] = "Recurring", "Recurring 10 - 99.99" ,
                 if(Sheet1[Brackets] = "B" && 'Sheet1'[hmg_gift_cycle] = "Recurring", "Recurring 100 - 249.99",
                 if(Sheet1[Brackets] = "C" && 'Sheet1'[hmg_gift_cycle] = "Recurring", "Recurring 500 - 999.99",
                 if(Sheet1[Brackets] = "D" && 'Sheet1'[hmg_gift_cycle] = "Recurring", "Recurring 1000 +",
                 if(Sheet1[Brackets] = "A" && 'Sheet1'[hmg_gift_cycle] = "Single", "Single 10 - 99.99" ,
                 if(Sheet1[Brackets] = "B" && 'Sheet1'[hmg_gift_cycle] = "Single", "Single 100 - 249.99",
                 if(Sheet1[Brackets] = "C" && 'Sheet1'[hmg_gift_cycle] = "Single", "Single 500 - 999.99",
                 if(Sheet1[Brackets] = "D" && 'Sheet1'[hmg_gift_cycle] = "Single", "Single 1000 +"))))))))
  • Waqas_BIspecs's avatar
    Waqas_BIspecs
    Frequent Visitor
    3 years ago

    This should definitely work for you.

    Single Query Solution =
    if(Sheet1[hmg_gift_amoount] >= 10 && Sheet1[hmg_gift_amoount] <= 99.99 && 'Sheet1'[hmg_gift_cycle] = "Recurring", "Recurring 10 - 99.99" ,
    if(Sheet1[hmg_gift_amoount] >= 100 && Sheet1[hmg_gift_amoount] <= 249.99 && 'Sheet1'[hmg_gift_cycle] = "Recurring", "Recurring 100 - 249.99",
    if(Sheet1[hmg_gift_amoount] >= 250 && Sheet1[hmg_gift_amoount] <= 999.99 && 'Sheet1'[hmg_gift_cycle] = "Recurring", "Recurring 250 - 999.99",
    if(Sheet1[hmg_gift_amoount] >= 100 && 'Sheet1'[hmg_gift_cycle] = "Recurring", "1000 + ",
    if(Sheet1[hmg_gift_amoount] >= 10 && Sheet1[hmg_gift_amoount] <= 499.99 && 'Sheet1'[hmg_gift_cycle] = "Single", "Single 10- 499.99",
     if(Sheet1[hmg_gift_amoount] >= 500 && Sheet1[hmg_gift_amoount] <= 999.99 && 'Sheet1'[hmg_gift_cycle] = "Single", "Single 500- 999.99",
    if(Sheet1[hmg_gift_amoount] >= 1000 && 'Sheet1'[hmg_gift_cycle] = "Single", "Single 1000+" )))))))
  • Bmejia's avatar
    Bmejia
    Super User
    3 years ago

    Can something like this work for you using a Switch True Statement

    NewColumn = SWITCH(TRUE(),
    ('Switch'[Gift Amount]>=10 && 'Switch'[Gift Amount]<99.99) && 'Switch'[Gift Cycle]="Recurring","Recurring 10-99.99",
    ('Switch'[Gift Amount]>=100 && 'Switch'[Gift Amount]<249.99) && 'Switch'[Gift Cycle]="Recurring","Recurring 100-249.99",
    ('Switch'[Gift Amount]>=10 && 'Switch'[Gift Amount]<499.99) && 'Switch'[Gift Cycle]="Single","Single 10-499.99",
    ('Switch'[Gift Amount]>=500 && 'Switch'[Gift Amount]<999.99) && 'Switch'[Gift Cycle]="Single","Single 500-999.99",
    "1000+"
    )