How to filter using the aggregated column after summarize

[NoaLowraee]

Hello,

I got a problem and I really want your suggestion.

I have this scenario

ClientId  Project  Creation_Project   platforme

A              PR1       01/01/2022                    P1

A              PR2       01/02/2022                    P2

B              PR3        01/01/2022                   P1

B              PR4        15/02/2022                   P1

I want this result : wich platform got me the first project 

First_touch

  P1 : 2

  P2 :  1

Last touch :   wich platform got me the last project 

 P1 : 1 

 P2 : 1 

In this case I need two measure  First_touch_Platform and Last_Touch_Platform 

wich consist on sumarazing by client and selecting the first / Last platform associated with the Min/max Date_creation 

I Did this  for the LastTouch ( the same logic for the firstTouch but unsing Min(client_plat[Creation_Project  ])

LT_PT = 

var TableLastTouch  = 

SUMMARIZECOLUMNS(

client_plat[clientID],

"Lt_Date", Max(client_plat[Creation_Project  ]),

"LastPlatform", CALCULATE(FIRSTNONBLANK(client_plat[Platform],F_PA[Platform]), FILTER(client_plat,client_plat[Creation_Project   ] =  Max(client_plat[Creation_Project   ])))

)

return countRows( TableLastTouch , TableLastTouch[LastPlatform]= selected value in slicer )

the last   TableLastTouch[LastPlatform] does not get reconized by Power BI.

Note : 

The calculation of the measure should not be affected by the Platform ( we need to filter after the calculation otherwise I will have this wich is not correct )

First_touch

  P1 : 2

  P2 :  1

Last touch :   wich platform got me the last project 

 P1 :  2 ==> false

 P2 : 1 

Is there any suggestion how can I filter my summarized table ( after having done the summarize) using the aggregated column  LastPlatform ?

Thank you

[amitchandak · Accepted solution]

@NoaLowraee , Try measures like

First Val=
var _min = calculate(min(Table[Creation_Project]), filter(allselected(Table), Table[Client] = min(Table[Client])))
return
CALCULATE(Count(Table[Project]), filter( (Table), Table[Client] = max(Table[Client]) && Client[Creation_Project] =_min))

Sum First Val = sumx(VALUES(Table[Client]) , [First Val])

Last Val=
var _min = calculate(max(Table[Creation_Project]), filter(allselected(Table), Table[Client] = min(Table[Client])))
return
CALCULATE(Count(Table[Project]), filter( (Table), Table[Client] = max(Table[Client]) && Client[Creation_Project] =_min))

Sum Last Val = sumx(VALUES(Table[Client]) , [Last Val])

refer

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[NoaLowraee]

Hello @amitchandak ,

Thank you for your response. it really helped. 

Working great