Convert SQL expression in M code INNER SELECT ?

[lottino]

Hello,

given structure of Table bellow:

C - - - ID - - - DATE

10 - - - 1 - - - 09/12/19

11 - - - 2 - - - 10/12/19

12 - - - 2 - - - 11/12/19

13 - - - 2 - - - 12/12/19

14 - - - 3 - - - 13/12/19

I'd like to add a column displaing C value in PBI query with condition to look for the grater-earliest date record on a multiple rows with same id.

So for example as per 12/12/19 I want to look for 11/12/19 and print its related C number (12).

And in case of date 10/12/19 since there is no earliest date for same id to print null.

In SQL expression would be something like this: 

SELECT id, date FROM Table

INNER JOIN (SELECT MAX(date) AS maxdate

                      FROM Table WHERE id=[id] AND date<[date]

                      GROUP BY 1

                      ORDER BY date DESC) a

ON a.maxdate=date

[date] and [id] rapresent each value as per PBI column.

Thanks

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, please try this formula to add a column in M query.

Test  = Table.AddColumn(#"Changed Type", "New Data", each let

id = [ID],

d = [DATE],

PreviousDate = Table.Max(Table.SelectRows(Table.SelectRows(#"Changed Type", each [ID] = id), each [DATE]<d),"DATE")

in if PreviousDate = null then null else PreviousDate[C])

Here is the result:

Here is my test pbix file.

pbix 

If you still have any questions about it, please for free to let me know.

Best Regards,

Giotto Zhi

[lottino]

Thank you for sending very helpfull!

Just faceing speed issues in loading data to model.

This might caused to each value check up to full compare collumn over each [ID] and [DATE].

How to possibelly skip values with signle [ID] entry and case for [ID] occurancy to run "M inner join select" as suggested.

Thank you,

Regards

[lottino]

Query is considerately slow tuck me almost 24h to load full results 1 record every 1.5 sec aprox

- How to solve this in time efficiency?

Thank you,

Best

[v-gizhi-msft]

Hi,

After my test, i think that using M code cannot improve time efficiency obviously.

And i advise you to use DAX expression to achieve the result due to its high efficiency.

Please create a column:

New Date Dax = 
VAR lastd =
    CALCULATE (
        MAX ( 'Test'[DATE] ),
        FILTER (
            'Test',
            [DATE] < EARLIER ( Test[DATE] )
                && [ID] = EARLIER ( Test[ID] )
        )
    )
RETURN
    IF (
        [DATE]
            = CALCULATE (
                MIN ( 'Test'[DATE] ),
                FILTER ( 'Test', [ID] = EARLIER ( Test[ID] ) )
            ),
        BLANK (),
        CALCULATE ( MAX ( [C] ), FILTER ( 'Test', [DATE] = lastd ) )
    )

And it shows:

Hope this helps.

Best Regards,

Giotto Zhi

[lottino]

Hello,

Given following DAX Expression

CALCULATE ( MAX ( [C] )

there is a a problem with FILTERs somewhere.

Case when original C equals to null and user ID occurancy is found it reports MAX [C] value of a different user ID result.

For instance:

ID   - -  origianl_C   - -  new_C

1   - - -    4   - - -   null

1  - - -    5   - - -    4

2   - - -   null   - - -   null

2  - - -    null   - - -    5

2   - - -   null   - - -    5

2  - - -    null   - - -    5

For user ID 2 occurancy 5 should state null instead of taking max C of ID 1.

- How to refresh MAX [C] back to null when C null value finds same ID over FILTER?

Thank you,

Regards

[lottino]

DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[lottino]

Hello,

not sure how to change a date to a value format.

- is this about what the error about?

Thank you

M

[v-gizhi-msft]

Hi,

Please try this calculated column:

Column = YEAR('Date'[Date])*10000+MONTH('Date'[Date])*100+DAY('Date'[Date])

Best Regards,

Giotto Zhi

[lottino]

Now Integer:

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

L

[lottino]

Solved:

FORMAT(YEAR('All Contract Logistic'[8.startDate])*10000+MONTH('All Contract Logistic'[8.startDate])*100+DAY('All Contract Logistic'[8.startDate]),"General Number")