cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
numetalsour
New Member

Filter table by conditional and last row by id

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

 

 

1 ACCEPTED SOLUTION
Mikelytics
Resolver III
Resolver III

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:

Picture 1.PNG

 

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.

 

picture 2.PNG

 

Hope this helps.

 

Best regards

Mikelytics

 

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

 

Appreciate your Kudos.

View solution in original post

2 REPLIES 2
PaulDBrown
Super User II
Super User II

@numetalsour 

Here is one way of doing this.

The model:

model.PNG

 

 

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:

result.PNG

 

I've attached below the PBIX file for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Mikelytics
Resolver III
Resolver III

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:

Picture 1.PNG

 

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.

 

picture 2.PNG

 

Hope this helps.

 

Best regards

Mikelytics

 

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

 

Appreciate your Kudos.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.