extract the whole row by [Key] and last [DateTime]

[irisava]

Hello,

I have a table with several fields (columns).

As in the attached picture, I have a table with 4 fields: [Key] [DateTime] [Price] [Quality]

I would like to extract a whole row based on the last [DateTime] for each group of [Key].

For this example I expect the result of:

Apple 2018-08-15 8 3

Banana 2018-08-15 4 2

In SQL I can group [Key] and select the last [DateTime].

However, since in PBI "Edit Query" the M queries took too long, I was suggested to use DAX with calculated tables and calculated columns instead.

I have tried the method posted in

https://community.powerbi.com/t5/Desktop/by-ID-amp-Last-Date/td-p/62931

For this method I created an additional table with only [Key] and [DateTime].

However, I still got an error, which I am not sure why this could happen, while creating the calculated table:

"The column 'IDwithDate[Dev ID]' either doesn't exist or doesn't have a relationship to any table available in the current context."

I really appreciate any help to resolve this problem. 

--------------------The codes are as follow-------------------

The original table: Threshold

The additional table:

IDwithDate = SELECTCOLUMNS(Threshold; "Dev ID"; [match key]; "Date"; [Threshold date])

The measure:

lastDate = LASTDATE(IDwithDate[Date])

The calculated table:

TableX = FILTER( Threshold;
                               ( (Threshold[match key]=RELATED('IDwithDate'[Dev ID]))
                                  && (Threshold[Threshold date]=RELATED('IDwithDate'[lastDate]))
                               )
                          )

[v-jiascu-msft]

Hi @irisava,

The attached picture is corrupted. Please update it. 

Seems you don't need to create a new table. You just need a measure to tag the rows and filter them out.

Measure =
VAR lastestDatetime =
    CALCULATE ( MAX ( 'table'[DateTime] ), ALLEXCEPT ( 'table', 'table'[key] ) )
RETURN
    IF ( MAX ( 'table'[DateTime] ) = lastestDatetime, 1, BLANK () )

In the normal situation, the unqualified rows would be hidden automatically.

Best Regards,

Dale

[irisava]

Hello Dale,

thank you for your time!
I am not familiar with DAX.

Could you please tell me if it is possible to add another filter based on DateTime in the measure?

And how to use this measure to create the calculated column?

More precisely, the problem description is as follow:

Following are the two tables in the format of my data.

I would like to append the columns [Threshold] and [Value1] of Table2 to Table1 based on 

1. same [Key] and

2. 'Table1'[DateTime] >'Table2'[DateTime]

Power Query is not feasible since the dataset are too large and group query took too long.

So I want to try calculated tables and columns in DAX.

Thanks again for your support.

[v-jiascu-msft]

Hi @irisava,

Does your real data look like this? Please check out the demo in the attachment.

Threshold =
VAR currentDatetime = [DateTime]
VAR currentKey = [Key]
VAR maxDate =
    CALCULATE (
        MAX ( 'Table2'[DateTime] ),
        FILTER (
            'Table2',
            'Table2'[DateTime] < currentDatetime
                && 'Table2'[Key] = currentKey
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table2'[Threshold] ),
        FILTER ( 'Table2', 'Table2'[Key] = currentKey && 'Table2'[DateTime] = maxDate )
    )

Value1 =
VAR currentDatetime = [DateTime]
VAR currentKey = [Key]
VAR maxDate =
    CALCULATE (
        MAX ( 'Table2'[DateTime] ),
        FILTER (
            'Table2',
            'Table2'[DateTime] < currentDatetime
                && 'Table2'[Key] = currentKey
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table2'[Valuel] ),
        FILTER ( 'Table2', 'Table2'[Key] = currentKey && 'Table2'[DateTime] = maxDate )
    )

Best Regards,
Dale

[irisava]

Hello Dale,

thank you very much for providing the demo with codes!

I really appreciate it. 🙂

The calculated column in DAX codes is adopted for my whole dataset. It took more than 3 hour and still not finish "working on it".

(And the memory and CPU consumptions are very high. )

I am afraid that it would be the same case as group query in Power Query...

Would you have any other ideas on the direction, how I could solve this problem with higher efficiency?

Thanks a lot for your time!

[v-jiascu-msft]

Hi @irisava,

What's the size of your dataset? The number of rows?

Try these two formulas, please. The part before "Return" can't be optimized for now.

Threshold 2 =
VAR currentDatetime = [DateTime]
VAR currentKey = [Key]
VAR maxDate =
    CALCULATE (
        MAX ( 'Table2'[DateTime] ),
        FILTER (
            'Table2',
            'Table2'[DateTime] < currentDatetime
                && 'Table2'[Key] = currentKey
        )
    )
RETURN
    LOOKUPVALUE (
        Table2[Threshold],
        Table2[DateTime], maxDate,
        Table2[Key], currentKey
    )

Value1 2 =
VAR currentDatetime = [DateTime]
VAR currentKey = [Key]
VAR maxDate =
    CALCULATE (
        MAX ( 'Table2'[DateTime] ),
        FILTER (
            'Table2',
            'Table2'[DateTime] < currentDatetime
                && 'Table2'[Key] = currentKey
        )
    )
RETURN
    LOOKUPVALUE (
        Table2[Valuel],
        Table2[DateTime], maxDate,
        Table2[Key], currentKey
    )

How about a measure? 

Best Regards,
Dale

[v-jiascu-msft]

Hi @irisava,

Did it work? 

Could you please mark the proper answers as solutions?

Best Regards,

Dale