Measure counting distinct values with condition

[EB09]

Hi, 

I'm having trouble trying to create a new column in my table. 

I have several person ID's in the same table, but I only want to count it once for each FY (financial year). 

I need a formula that results in a '1' per row.  I hope the example below with my desired column explains what I need if anybody can please help.

Person ID       FY     (Desired Column)

1                   2024   1

1                   2024   0

2                   2024   1

2                   2024   0

2                   2024   0

1                   2023   1

1                   2023   0

1                   2023   0 

2                   2023   1

2                   2023   0 

2                   2023   0 

3                   2023   1

3                   2023   0

Appreciate any help!!

[Anonymous · Accepted solution]

Hi @EB09 

First you need to add the index column in the Power Query editor as shown below.

Then You can create a measure as follows.

result =
VAR _min =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Person ID], 'Table'[FY] )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Index] ) = _min, 1, 0 )

Is this the result you expect?

Best Regards,

Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EB09]

Sorry I'm a new user I'm not too sure, is there anyway this can be done from what I've said? Do I need to structure in a different way?

[Fowmy]

@EB09 

is there any other columns in this table that would identify the order or the sort. What differentiates each line when they are the same ID and the FY?