Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help with DAX formula into loop

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!
 
DAX
Dax Help
Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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]
    )
     
     
     

    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

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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]
    )
     
     
     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        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.