CODEWITHSUNDEEP
powerbi
PV8
PV8

Reputation: 6260

Occurence of a ID in every Year in PowerBI

I have a data on a monthly base in PowerBI, which looks like this:

ID  Month  FY
A    1     TY
A    2     TY
A    1     LY
B    1     TY
B    2     TY

How can I check if the ID occurs in every FY, in a new column?

ID  Month  FY  Marker
A    1     TY  y
A    2     TY  y
A    1     LY  y
B    1     TY  n
B    2     TY  n

I try to use summarize, but I have no idea how to continue:

New column= summarize(data, ID, FY, "Count", distinctcount(ID))

Upvotes: 0

Views: 28

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Try with this below measure -

Marker = 

VAR current_row_id = your_table_name[ID]

VAR fy_count_total =
CALCULATE(
    DISTINCTCOUNT(your_table_name[FY]),
    ALL(your_table_name)
)

VAR fy_count_for_id =
CALCULATE(
    DISTINCTCOUNT(your_table_name[FY]),
    FILTER(
        ALL(your_table_name),
        your_table_name[ID] = current_row_id
    )
)

RETURN 
IF(
    fy_count_total = fy_count_for_id , 
    "Y", 
    "N"
)

Upvotes: 1

Related Questions