Value, Earlier, Allexcept ?

[propi_]

Hey guys,

can you help me with the following please:

I want to figuere out the first and second Order for each Customer and put it to a column.

ID	Customer_id	Order_of_n_day	OrderDay	Orderstring	Revenue	Date	Order no	Category	FirstDayfirstOrder	SecondDay1Order
1	100	1	1	11	27	29.07.2015 00:01	885689	V	V	S
2	100	2	1	21	22,36	29.07.2015 00:05	885690	F	V	S
3	100	3	1	31	49,19	29.07.2015 00:06	885691	P	V	S
4	100	4	1	41	18,02	29.07.2015 00:07	885692	F	V	S
5	100	1	2	12	108,4	01.09.2015 00:01	885693	S	V	S
6	100	1	3	13	27	07.11.2016 00:01	885694	V	V	S
7	100	2	3	23	60,17	07.11.2016 00:05	885695	P	V	S
8	100	3	3	33	100	07.11.2016 00:06	885696	V	V	S
9	200	1	1	11	27	29.08.2015 00:01	885697	X	X	Z
10	200	2	1	21	22,36	29.08.2015 00:05	885698	F	X	Z
11	200	3	1	31	50	29.08.2015 00:06	885699	P	X	Z
12	200	4	1	41	18,02	29.08.2015 00:07	885700	F	X	Z
13	200	1	2	12	200	01.10.2015 00:01	885701	Z	X	Z
14	200	1	3	13	27	17.11.2016 00:01	885702	V	X	Z
15	200	2	3	23	60,17	17.11.2016 00:05	885703	P	X	Z
16	200	3	3	33	200	17.11.2016 00:06	885704	V	X	Z

<tbody>
</tbody>

Thank you guy, you saved me some extra joins in SQL
Not sure if the DAX Engine performs faster than an SQL Statement...

Greets
Andy

 

[propi_]

Any News on that? Glad if you could help

 

[Ozeroth]

Hi Andy,

Just to interpret what you are wanting to calculate:

1. For the current Customer, return he Category of the first order on the first date where that Customer placed an order.
2. For the current Customer, return he Category of the first order on the second date where that Customer placed an order.

Using the index columns you already have, there are a few options:

[B]FirstDayFirstOrder[/B]
=
CALCULATE (
    FIRSTNONBLANK ( Data[Category], 0 ),
    ALLEXCEPT ( Data, Data[Customer_id] ),
    Data[OrderDay] = 1,
    Data[Order_of_n_day] = 1
)

or

[B]FirstDayFirstOrder[/B]
=
CALCULATE (
    FIRSTNONBLANK ( Data[Category], 0 ),
    ALLEXCEPT ( Data, Data[Customer_id] ),
    Data[OrderString] = "11"
)

or

[B]FirstDayFirstOrder[/B]
=
LOOKUPVALUE (
    Data[Category],
    Data[Customer_id], Data[Customer_id],
    Data[Orderstring], "11"
)

The SecondDayFirstOrder measure would be the same but with Data[OrderDay] = 2 or Data[OrderString] = "12"

I assume, since you are adding calculated columns, you will want to filter by these columns in your report at some point?

Regards,
Owen

 

[propi_]

Hi Owen,

yes filter on these columns were the goal...
Man you're the best! FIRSTNONBLANK() was the key for me.
Even the LOOKUPVALUE() makes it easy though...

You've got my MVP Vote

Regards
Andy