Filter table by conditional and last row by id

[numetalsour]

Hey

Iam trying to learn power bi, and i want do so something like the following

(code works like id)

code  |  process   | date

qwe     updated    2020/12/01   20:00:00

qwe     finished     2020/12/02  10:00:00

qwe     updated    2020/12/03  15:00:00

asd      updated    2021/01/10  10:00:00

asd      reject         2021/01/10  11:00:00
zxc      (null)          2021/01/10   12:00:00
lkp      new            2021/01/15   15:00:00

lkp      updated     2021/01/15    16:00:00

output

code  |  process   | date

qwe     finished     2020/12/02  10:00:00

asd      reject         2021/01/10  11:00:00
zxc      (blank)          2021/01/10   12:00:00

lkp      updated     2021/01/15    16:00:00

if some row with same code have "finished" process, its taken like last even if have more process

Thanks a lot

[PaulDBrown]

@numetalsour 

Here is one way of doing this.

The model:

And the measure to be used in the "Filters on this visual" in the filter pane:

Select rows =
VAR MaxDate =
    CALCULATE (
        MAX ( 'DataTable'[Date Time] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Code] )
    ) //Identifes the max DateTime for each code
VAR Finished =
    CALCULATETABLE (
        VALUES ( 'DataTable'[Code] ),
        FILTER ( ALL ( 'DataTable' ), 'DataTable'[Process] = "finished" )
    ) //creates a table of codes which include a Process as "finished"
VAR Other =
    VALUES ( 'DataTable'[Code] ) // Creates a table for all codes
VAR Filt =
    EXCEPT ( Other, Finished ) // creates a table with codes which do not include a "finished" process 
VAR FinishedRows =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'DataTable',
            "Code", 'DataTable'[Code],
            "Process", 'DataTable'[Process],
            "Date Time", 'DataTable'[Date Time]
        ),
        'DataTable'[Process] = "finished"
    ) // creates a table with columns needed for rows which include "finished"
VAR OtherRows =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'DataTable',
            "Code", 'DataTable'[Code],
            "Process", 'DataTable'[Process],
            "Date Time", 'DataTable'[Date Time]
        ),
        FILTER (
            'DataTable',
            COUNTROWS ( Filt ) = 1
                && 'DataTable'[Date Time] = MaxDate
        )
    ) //creates a table with columns for rows with codes which have no "finished" process and only for the max date for each code.
RETURN
    COUNTROWS ( UNION ( OtherRows, FinishedRows ) )
// calculates the number of rows for a table appending the 'FinsihedRows' table and 'OtherRows' table

And you get this:

I've attached below the PBIX file for your reference

[Mikelytics · Accepted solution]

Dear @numetalsour ,

I think I might have a solution. As an example I use a dataset with 5 different products. and in the end I identify the last row for each product based on the date using two calulcated columns.

Calculated Column 1:

RANK of Row per product = 
RANKX(
    FILTER(
        ALL(fact_table_d),
        fact_table_d[Product] = EARLIER(fact_table_d[Product])
        )
    ,fact_table_d[Date],
    ,ASC)

see the result:

Calulcated Column 2:

LAST ROW OF PRODUCT = 
if(
    CALCULATE(
        MAX(fact_table_d[RANK of Row per product]),
        FILTER(
            ALL(fact_table_d),
            fact_table_d[Product]=EARLIER(fact_table_d[Product])
        )
    )
=
fact_table_d[RANK of Row per product],
"x",
BLANK())

see the results. I can now filter for the last sale per product) :

-> if you wonder, there is no product D in the dataset. I checked it.

Hope this helps.

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution.

Appreciate your Kudos.