cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irisava
Regular Visitor

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

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

6 REPLIES 6
v-jiascu-msft
Microsoft
Microsoft

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

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

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]

 

 

2 tables.jpg

 

 

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.

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

extract_the_whole_row

Best Regards,
Dale

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

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!

 

 

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

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

Hi @irisava,

 

Did it work? 

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors