topic Re: Help with DAX formula into loop in DAX Commands and Tips

Help with DAX formula into loop

Anonymous — Fri, 28 May 2021 07:25:46 GMT

Hello all,

I have a question about my power query DAX formula. Below is my SWITCH and I want to put this in a loop.
I want to constantly increase my loop by 2.5 because I have a lot of different values from 0 to 1000.

Bedrag =

SWITCH (

TRUE(),

Order[Amount Order] >= 0 && Order[Amount Order] < 5 , "< 5",

Order[Amount Order] >= 5.1 && Order[Amount Order] <= 7.5, "5 - 7.5",

Order[Amount Order] >= 7.51 && Order[Amount Order] <= 10, "7.51 - 10",

Order[Amount Order] >= 10.01 && Order[Amount Order] <= 12.5, "10.01 - 12.5",

Order[Amount Order] >= 12.51 && Order[Amount Order] <= 15, "12.51 - 15",Blank())

I can't figure it out myself, can someone help me.

Thank You!

Re: Help with DAX formula into loop

Jihwan_Kim — Fri, 28 May 2021 08:11:29 GMT

Hi, Anonymous

I am not sure if I understood your question correctly, but I suggest having a grouping table like below.

Please check the below picture and the sample pbix file's link down below.

I tried to create a sample pbix file based on the explanation. And the below type of grouping table is easy to create in excel and it can be imported into Power BI easily.

Qty Total by Group =

SUMX (

FILTER (

VALUES ( Sales[Date] ),

COUNTROWS (

FILTER (

'Grouping',

[Qty Total] > 'Grouping'[Min]

&& [Qty Total] <= 'Grouping'[Max]

)

) > 0

[Qty Total]

)

https://www.dropbox.com/s/n58wg8fknnmwohx/s110.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Help with DAX formula into loop

Anonymous — Fri, 28 May 2021 08:27:14 GMT

Thanks a lot, That's exactly what I want!! 😀

Re: Help with DAX formula into loop

Anonymous — Fri, 28 May 2021 12:12:49 GMT

Hi Jihwan,

I see that my analysis is not quite consistent with what you sent, but we are almost there.

The data I have:
I have orders with a total order amount. Now they want to have the number of orders that fall into the categories 0 - 2.50, 2.51 - 5, et cetera. So what I have to do is count the number of orders which is between the amounts 0 - 2.50 et cetera.
Your answer is very similar to my question, but I get to see the sum of the total amount. I actually need the sum of all the orders in the different categories.

I don't quite understand the second table you sent me.

I see in the right table that there is only 1 order with a qty of 5 and your analysis there are 5.
I see there is only 1 qty of 12 and your analysis there are 12.
between 12.5 and 15 I only see 2 and your analysis there are 28.

Do you understand what I mean?

Thans for you answer!!

Re: Help with DAX formula into loop

Jihwan_Kim — Fri, 28 May 2021 12:46:56 GMT

Hi, Anonymous

If it is OK with you, please share your sample pbix file's link here, together with the desired outcome that you want to see. Then I can try to look into it to come up with a more accurate measure.

Thanks.

Re: Help with DAX formula into loop

Anonymous — Fri, 28 May 2021 14:16:42 GMT

Ps. remove relation

https://www.dropbox.com/s/tp9lrpcf6uukzo0/PowerBiToCommunityQuestion.pbix?dl=0

Re: Help with DAX formula into loop

Jihwan_Kim — Fri, 28 May 2021 14:57:56 GMT

Hi,

Thank you for sharing.

Sorry that I quite do not understand.

I see that 0 <= <=5 : count of orders => 36

but your desired outcome seems to show a different number.