topic First Occurrence of a Value in DAX Commands and Tips

First Occurrence of a Value

djdubbert — Thu, 15 Jul 2021 14:40:37 GMT

I want to create a new column with a 1 when the 1st occurence of the 'Occurrences UID' column happens, and the new column to have a 0 for all other instances. In excel the formula is: =IF(COUNTIF($AO$2:AO2,AO2)>1,0,1). How could I accomplish this in Power BI?

Re: First Occurrence of a Value

PaulOlding — Thu, 15 Jul 2021 15:44:36 GMT

How do you decide which is the first? ie how is the list sorted?

In this example I'm sorting by a column called amt. You can change that for however you want to sort it. RANKX also has extra parameters for ordering ascending or descending if you need it.

First =

VAR _Current = 'Table'[Occurences UID]

VAR _Rank =

RANKX(

FILTER('Table',

'Table'[Occurences UID] = _Current

'Table'[amt]

)

VAR _Result = IF(_Rank = 1, 1, 0)

RETURN

_Result

Re: First Occurrence of a Value

djdubbert — Thu, 15 Jul 2021 18:07:26 GMT

This worked! Thank you for your help!