DAX code

[Anonymous]

Hello,

What commands could be used to write a code from the data that divides all orders under 25 euros from the customer's order and would suggest the nearest delivery date for the nearest order of the same customer? 🙂

[amitchandak]

@Anonymous , that seems like few questions merged into one.

Can you explain with example

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

I can’t put in the right information, but I made a small table that shows the relevant information.

I would like this to be as automatic as possible so there is no need to make multiple choices.

For example:

Shows the order number and the date+ order number of the nearest order, if the order amount is less than 25 and the address is same.

So, in this table, measure would suggest that order 122 can be combined with order 878. Because the order sum of order 122 is less than 25 and the delivery addresses are the same (Finland) and 5.3 is the date of the nearest shipment. 

Hope this clarified my problem a bit. Thank you very much for your answer 🙂

[v-janeyg-msft]

Hi, @Anonymous 

Your description and screenshots seem to be conflicting. There is no number less than 25 and 878 is not the latest order from Finland. Can you explain it more clearly? So we can help you soon.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I'll start from the beginning. I don't know if this is possible with power BI but there is a company. The company has a certain number of subscribers who order products from them constantly. What this company would like to know is that smaller orders from their customers can be combined into a single delivery. What information has been given to me is e.g. orders, desired delivery times as well as product prices and delivery addresses. I'm trying to solve the problem, can I have some counter automated that order aggregation? For orders under 25 euros, I have created a counter that identifies orders under 25 euros, and with the choices made by the user, he will be able to see all customer orders and a note about merging. I was wondering if this could be done in such a way that the user would have to make as few choices as possible or even see on the screen which incoming orders are small and automatically suggest merging the order with another order and telling the machine which order is worth less than 25 euros.

That Excel was made in my language so in here (,) mean same than Americas (.)

I'm sorry my English language skills aren't very good so understanding can be challenging

[v-janeyg-msft]

Hi, @Anonymous 

For your description, what is the concept of merging, simply sum? In the process of trying to solve the problem, I ran into a problem, because there are some situations you didn't explain.

Like:

I make an example. for example, 22 in the figure, should it be merged with 30 or 35? Also, if the last order is less than 25, should it be merged with the previous order greater than 25? I need a complete logic to create a measure. Or, do you only need to highlight orders less than 25?

Best Regards

Janey Guo

[Anonymous]

Hello,

I think it should be combined according to the delivery date. In other words, if the customer has placed an order for less than 25 euros, it can be combined with the nearest delivery. That is, the delivery date of the product is either earlier or slightly delayed, depending on the delivery date of the next second order 🙂

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

I create two measures, one is to show sum and one is to filter data in filter pane.

Like this:

sum =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[address],
        'Table'[date],
        'Table'[Order mumber],
        "sum",//Using summarize is to ensure that total is correct. If you don’t mind total, just use the code in "sum"
            VAR a =
                MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        [address] = SELECTEDVALUE ( 'Table'[address] )
                            && [order sum] >= 25
                            && [Order mumber] = SELECTEDVALUE ( 'Table'[Order mumber] )
                    ),
                    [date]
                )
            VAR b =
                MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        [address] = SELECTEDVALUE ( 'Table'[address] )
                            && [order sum] >= 25
                            && [date] < a
                    ),
                    [date]
                )
            VAR c =
                SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        [address] = SELECTEDVALUE ( 'Table'[address] )
                            && [date] > b
                            && [date] <= a
                    ),
                    [order sum]
                )
            RETURN
                IF (
                    MAXX (
                        FILTER ( ALL ( 'Table' ), [address] = SELECTEDVALUE ( 'Table'[address] ) ),
                        [order sum]
                    ) < 25,
                    MAX ( 'Table'[order sum] ),
                    c
                )
    ),
    [sum]
)

show = IF([sum]=BLANK(),0,1)

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I am so grateful. Thank you a thousand times 😍

[v-janeyg-msft]

Hi, @Anonymous Okay, I understand your needs, and I will get back to you tomorrow.