Creating one Dax formula for multiple IF statements

[Anonymous]

@ 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! 

[Waqas_BIspecs]

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+" )))))))

[Waqas_BIspecs]

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 +"))))))))

[Bmejia]

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+"

)

[Waqas_BIspecs]

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]

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]

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]

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