GroupBy to get minimum of several fields

[mdlm778]

Hi everybody !

This is my first post 

So first of all, sorry for the presentation in this post, I don't know how to draw a table, I selected Insert Code but it's not the good way I think.

I'm totally newbie on PowerBI. I've watched some tutorials videos on youtube, I've read some articles but I have to admit: I still don't get the DAX formulas, how to use them, … So I hope you could help me resolve my problem.

Here is one sample of my table named MyTable (actually there is more columns but not useful right now) :

DateZ	Key	DateX
01/02/2018	A1	07/02/2018
02/02/2018	A1	05/02/2018
05/02/2018	A1	05/02/2018
01/02/2018	A2	06/02/2018
02/02/2018	A2	06/02/2018
05/02/2018	A2	06/02/2018
06/02/2018	A2	06/02/2018
01/02/2018	A3	08/02/2018
02/02/2018	A3	10/02/2018
05/02/2018	A3	08/02/2018
12/02/2018	A3	12/02/2018
05/03/2018	A3	12/02/2018
07/03/2018	A3	13/02/2018
16/03/2018	A3	15/02/2018

And here is what I'd like to obtain :

Key	DateZi	DateXi	DateZf	DateXf	NumberOfChangesDateX	DateZLastChangeDateX
A1	01/02/2018	07/02/2018	05/02/2018	05/02/2018	1	02/02/2018
A2	01/02/2018	06/02/2018	06/02/2018	06/02/2018	0	01/02/2018
A3	01/02/2018	08/02/2018	16/03/2018	15/02/2018	5	16/03/2018

And now let me explain everything:

1. For each key, I need to get the minimum of DateZ, with the associated DateX (called DateXi in my final table).
2. For each key, I need to get the maximum of DateZ, with the associated DateX (called DateXf in my final table).
3. For each key, I need to know how many times my DateX has changed (whatever the value is, I need to know from one row to the next one if there has been a change in the date; even if my DateX at row n+4 is equal to DateX as row n, I want my count to be +1).
4. For each key, I need to know what the DateZ is when the last change of DateX occurs (if no change of DateX I will take the minimum of DateZ).

So, once step at a time. I tried step 1 and I already failed 

Here is what I've done:

MyMinDates = GROUPBY(MyTable; MyTable [Key];"DateZi";MINX(CURRENTGROUP();[DateZ]) ; "DateXi";MINX(CURRENTGROUP();[DateX]))

But I obtain for key A1 the minimum of DateZ within the 3 lines, associated with the minimum of DateX within the 3 lines, which means:

A1

DateZi = 01/02/2018 ' good

DateXi = 05/02/2018 ' not good, I want 07/02/2018

So, how can I do ?

Thanks in advance for your help,

Have a good day !

[Zubair_Muhammad]

HI @mdlm778

This looks a tough question

But I believe this solution would work

1) First Add an Index Column in your Table

2) Next Add the following Calculated Column

ChangeInDateX =
VAR Previous_date =
    CALCULATE (
        VALUES ( MyTable[DateX] ),
        FILTER (
            ALLEXCEPT ( MyTable, MyTable[Key] ),
            MyTable[Index]
                = EARLIER ( MyTable[Index] ) - 1
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( Previous_date ) ) && MyTable[DateX] <> Previous_date, 1 )

[Zubair_Muhammad]

@mdlm778

Now this Calculated Table hopefully will give you the desired results

Table =
VAR T1 =
    SUMMARIZE (
        MyTable,
        MyTable[Key],
        "DateZi", MIN ( MyTable[DateZ] ),
        "DateZf", MAX ( MyTable[DateZ] ),
        "NumberOfChangesDateX", SUM ( MyTable[ChangeInDateX] )
    )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "DateXi", LOOKUPVALUE ( MyTable[DateX], MyTable[DateZ], [DateZi], MyTable[Key], [Key] ),
        "DateXf", LOOKUPVALUE ( MyTable[DateX], MyTable[DateZ], [DateZf], MyTable[Key], [Key] )
    )
VAR T3 =
    ADDCOLUMNS (
        T2,
        "Z",
        VAR LastChange =
            CALCULATE (
                MAX ( MyTable[Index] ),
                FILTER ( ALLEXCEPT ( MyTable, MyTable[Key] ), MyTable[ChangeInDateX] = 1 )
            )
        VAR MinOfX =
            CALCULATE ( MIN ( MyTable[DateX] ), ALLEXCEPT ( MyTable, MyTable[Key] ) )
        RETURN
            IF (
                ISBLANK ( LastChange ),
                CALCULATE (
                    MIN ( MyTable[DateZ] ),
                    FILTER ( ALLEXCEPT ( MyTable, MyTable[Key] ), MyTable[DateX] = MinOfX )
                ),
                CALCULATE (
                    MIN ( MyTable[DateZ] ),
                    FILTER ( ALLEXCEPT ( MyTable, MyTable[Key] ), MyTable[Index] = LastChange )
                )
            )
    )
RETURN
    T3

[Zubair_Muhammad · Accepted solution]

@mdlm778

Please see the attached Pbix file

[mdlm778]

Oh my God !!!

@Zubair_Muhammad You're awesome !!!

Thank you so much !

I would definitely not find the solution by myself. I couldn't imagine this could be that difficult. You're a genius.

To be honest, I need some time to understand everything  but I hope one day I will be able to do as you do, right first time 

Thank you very much