Forum Discussion

markpendlebury's avatar
markpendlebury
Frequent Visitor
9 years ago
Solved

Remove Rows in Dataset based on most recent ID and email address

Hi everyone hope someone can help me.....I'm pretty new to PowerBI but loving how flexible it is. However, as anything new I've come up against something I think should be pretty simple.   I have a...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi markpendlebury

     

    I think this calculated table might be getting close.  Just replace my Table2 with the name of your table

     

    Table 3 = SELECTCOLUMNS(
            FILTER(
                CROSSJOIN(
                    SELECTCOLUMNS( 
                        SUMMARIZECOLUMNS(
                            'Table2'[Email],
                            "MAX ID",
                            MAX('Table2'[ID])
                            ),
                            "MAX Email",[Email],
                            "MAX ID",[MAX ID]
                          ) ,
            'Table2'
            ),
            'Table2'[Email]=[MAX Email] && 
            Table2[ID] = [MAX ID]
            ),
            "ID",[ID],
            "Email" , [MAX Email]
            )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi markpendlebury

 

I think this calculated table might be getting close.  Just replace my Table2 with the name of your table

 

Table 3 = SELECTCOLUMNS(
            FILTER(
                CROSSJOIN(
                    SELECTCOLUMNS( 
                        SUMMARIZECOLUMNS(
                            'Table2'[Email],
                            "MAX ID",
                            MAX('Table2'[ID])
                            ),
                            "MAX Email",[Email],
                            "MAX ID",[MAX ID]
                          ) ,
            'Table2'
            ),
            'Table2'[Email]=[MAX Email] && 
            Table2[ID] = [MAX ID]
            ),
            "ID",[ID],
            "Email" , [MAX Email]
            )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Or you could go the calculated column way and have a column that shows a 1 or 0 depending if the row is the latest.

 

Just add the following calcualted column and then you can filter on it in the Report, Page or Visual filters.

 

Is Latest = IF( CALCULATE(
                COUNTROWS('Table2'),
                FILTER(
                    ALL(Table2),
                    'Table2'[ID] > EARLIER('Table2'[ID]) 
                    && 'Table2'[Email] = EARLIER('Table2'[Email])
                    )
                    )+0=0,1,0)