Flag the first occurrence of Table Name in each Environment

[alice11987]

Hi, I would like to create an additional column to flag the first occurence of duplicate Table Name in each Environment as below:

say table name ABC appeared first in 1 Jan 2023 in DEV env, it should be flagged "1"

and this table can appear again in UAT env, it should be flagged as "1" again in its first appeared in UAT env.

how should I do it, either in DAX or PQuery?

thank you

[dk_dk · Accepted solution]

Hi, @alice11987 

I have a solution with DAX Calculated Column:

Flag = 

VAR ename = Example[ENV]
VAR tname = Example[TABLE]
VAR firstocc = CALCULATE(MIN(Example[CREATED]),ALL(),Example[ENV]=ename,Example[TABLE]=tname)
RETURN
IF(Example[CREATED]=firstocc,1,0)

 
my results:

Hope this helps 🙂