Forum Discussion

EB09's avatar
EB09
Frequent Visitor
2 years ago
Solved

Measure counting distinct values with condition

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!!

 

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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?

  • EB09's avatar
    EB09
    Frequent Visitor
    2 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.