Create on the fly table based on filter result

[Fon]

All Hi,

I am new to Power Bi.

I have the following data:

id	date	channel	Result
1	1/1/2019	A	I
1	1/2/2019	B	J
1	1/3/2019	C	K
3	1/3/2019	A	I
4	1/4/2019	B	K
4	1/5/2019	C	J

Assume that there is a slicer that contains the column date.

Is there any way to get the entire row for each ID where date is the latest, and then slice by field Channel? Using the following measure i am able to calculate the distinct  id's   based on the date slicer.

Measure1 =

COUNTX (

VALUES ( Table1[id] );

CALCULATE ( DISTINCTCOUNT ( Table1[id] ); LASTDATE ( Table1[Date] ) )

)

Between 1/1/2019 and 1/3/2019  there are 2 id's (id =1 has channel  C  and id=3 has channel A).  If i choose channel=A, i will get  2 id's again. This happens because the filter channel=A is applied before the calculation of the measure1.

Is there any way to keep the records  where the date is the latest, and then slice by another field ?

Appreciate your feedback.

Regards,

Xenofon

[v-cherch-msft · Accepted solution]

Hi @Fon 

You may create a rank measure and use it in visual level filter like below.

Rank =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[id] = MAX ( 'Table'[id] ) ),
    CALCULATE ( MAX ( 'Table'[date] ) ),
    ,
    DESC
)

Regards,