Forum Discussion

Fon's avatar
Fon
Frequent Visitor
7 years ago
Solved

Create on the fly table based on filter result

All Hi,

 

I am new to Power Bi.

 

I have the following data:

iddatechannelResult
11/1/2019AI
11/2/2019BJ
11/3/2019CK
31/3/2019AI
41/4/2019BK
41/5/2019CJ

 

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

 

 

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,