Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Find matching values within same column with calculated measure and condition

Hi,

 

I am trying to find and match identical values within same column based on a calcualted measure and condition.

 

I tried two formulas:

 

Match = VAR R1 =
FILTER(DISTINCT(targetbillboard_enroute_master_table[deviceid]),[Rank] = 1)
RETURN
IF(CALCULATE(COUNTROWS(targetbillboard_enroute_master_table)
,FILTER(targetbillboard_enroute_master_table,targetbillboard_enroute_master_table[deviceid] IN R1)
) = 1,"Yes","No")
 
This one does not work.
 
Match = VAR R1 =
{"acv12","dscvfde","asde23"}
RETURN
IF(CALCULATE(COUNTROWS(targetbillboard_enroute_master_table)
,FILTER(targetbillboard_enroute_master_table,targetbillboard_enroute_master_table[deviceid] IN R1)
) = 1,"Yes","No")
 
This one works but rahter than using a static array i want to pass a column of string values which has a Rank 1 and check if those string values exists in the same column under rank > 1.
 
Any help will be highly appreciated.
 
Regards,
Waqas
1 ACCEPTED SOLUTION

@Anonymous 

 

Try this as VAR R1

 

VAR R1 =
CALCULATETABLE (
    VALUES ( targetbillboard_enroute_master_table[deviceid] ),
    FILTER ( ALL ( targetbillboard_enroute_master_table ), [RANK_] = 1 )
)

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Mate, please provide a link to a file with data and the data model.

 

This line is not clear enough:

 

This one works but rather than using a static array I want to pass a column of string values which have Rank 1 and check if those string values exist in the same column under rank > 1.

 

Without seeing the model it's not very likely anyone will tell you how to do what you want.

 

Best

Darek

Anonymous
Not applicable

Hi,

 

In the attached excel there are device ids, reach number and rank. I want to create a dynamic measure using variables which stores all the device ids with rank = 1 and compare thsoe device ids against all the device ids where rank is > 1. If there is match return "Yes" else "No.

 

idregiondeviceidReach_numberRank
2595Otago285BD29D-A499-4EE6-9E01-46F550A9D62651
2595Otago875F30A6-CB03-4C95-9681-3DA18D76587A51
2595Otago8E0AACF7-B8F7-44A4-B829-6C58FEA8A1A251
2595Otago8EA8F8AD-B26D-44EA-A735-D54F6F9B549751
2595OtagoF722D717-D437-4DED-A9AD-AD4C1A04660551
1462Otago1A303C734AC968091DC736C84EC96B4E32
1462Otago9B74D593-EE52-4379-88AA-01442BAD5FCC32
1462OtagoC487A32B-1CAA-4E4D-B02C-A2CC1A47E4C332
1493Otago2EC554E2-E594-435C-8F38-4B369246DD6932
1493Otago7C5D7BB0-8DE0-4782-830D-60717AD1543732
1493Otago95D1E56C-855A-4C06-A603-BAF68DCEBE9832
1494Otago17405FE7-98ED-4E82-A162-20D234C2F6B132
1494Otago3CD60B90-9407-452D-84F2-9439EC2EF19D32
1494OtagoEB2739C7-E451-4B4B-8F25-DAB8E19F3B6132
1459Otago2018A987-3D85-4CC7-A138-14E7A90A2D0625
1459Otago7EE8A3E7-34E1-49D0-B9B2-6E5B20AC587F25
1463Otago1A303C734AC968091DC736C84EC96B4E25
1463OtagoC487A32B-1CAA-4E4D-B02C-A2CC1A47E4C325
1467Otago875F30A6-CB03-4C95-9681-3DA18D76587A25
1467Otago8E0AACF7-B8F7-44A4-B829-6C58FEA8A1A225
1475OtagoA8966904-D595-4BEC-B503-66CA01C990CC25
1475OtagoB9EC82B3-A9B0-4D40-979C-2902BE5539E425
1478Otago26F4B9CF-6643-4FAC-BAFF-1974DF98899925
1478Otago38F051AF-CD63-4A79-BFCE-F5C49C34707525
1489Otago7C29E784A2AC117FBB66D478B05C678F0F1ADC6225
1489OtagoE73468E0-5F7E-44D3-8D3C-C4739863823325

 

Regards,

Waqas

Anonymous
Not applicable

Hi,

 

Please note that the Rank column in above table is a measure not a calculated column. And i am looking for solution with a measure not a calculated column because these calcualtions needs to be dynamic.

 

Regards,

Waqas

@Anonymous 

 

Try this as VAR R1

 

VAR R1 =
CALCULATETABLE (
    VALUES ( targetbillboard_enroute_master_table[deviceid] ),
    FILTER ( ALL ( targetbillboard_enroute_master_table ), [RANK_] = 1 )
)
Anonymous
Not applicable

Hi,

@Zubair_Muhammad  Thanks for replying with a solution. This works. Amazing.

 

Regards,

Waqas

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.