Find matching values within same column with calculated measure and condition

[Anonymous]

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

[Anonymous]

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]

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.

id	region	deviceid	Reach_number	Rank
2595	Otago	285BD29D-A499-4EE6-9E01-46F550A9D626	5	1
2595	Otago	875F30A6-CB03-4C95-9681-3DA18D76587A	5	1
2595	Otago	8E0AACF7-B8F7-44A4-B829-6C58FEA8A1A2	5	1
2595	Otago	8EA8F8AD-B26D-44EA-A735-D54F6F9B5497	5	1
2595	Otago	F722D717-D437-4DED-A9AD-AD4C1A046605	5	1
1462	Otago	1A303C734AC968091DC736C84EC96B4E	3	2
1462	Otago	9B74D593-EE52-4379-88AA-01442BAD5FCC	3	2
1462	Otago	C487A32B-1CAA-4E4D-B02C-A2CC1A47E4C3	3	2
1493	Otago	2EC554E2-E594-435C-8F38-4B369246DD69	3	2
1493	Otago	7C5D7BB0-8DE0-4782-830D-60717AD15437	3	2
1493	Otago	95D1E56C-855A-4C06-A603-BAF68DCEBE98	3	2
1494	Otago	17405FE7-98ED-4E82-A162-20D234C2F6B1	3	2
1494	Otago	3CD60B90-9407-452D-84F2-9439EC2EF19D	3	2
1494	Otago	EB2739C7-E451-4B4B-8F25-DAB8E19F3B61	3	2
1459	Otago	2018A987-3D85-4CC7-A138-14E7A90A2D06	2	5
1459	Otago	7EE8A3E7-34E1-49D0-B9B2-6E5B20AC587F	2	5
1463	Otago	1A303C734AC968091DC736C84EC96B4E	2	5
1463	Otago	C487A32B-1CAA-4E4D-B02C-A2CC1A47E4C3	2	5
1467	Otago	875F30A6-CB03-4C95-9681-3DA18D76587A	2	5
1467	Otago	8E0AACF7-B8F7-44A4-B829-6C58FEA8A1A2	2	5
1475	Otago	A8966904-D595-4BEC-B503-66CA01C990CC	2	5
1475	Otago	B9EC82B3-A9B0-4D40-979C-2902BE5539E4	2	5
1478	Otago	26F4B9CF-6643-4FAC-BAFF-1974DF988999	2	5
1478	Otago	38F051AF-CD63-4A79-BFCE-F5C49C347075	2	5
1489	Otago	7C29E784A2AC117FBB66D478B05C678F0F1ADC62	2	5
1489	Otago	E73468E0-5F7E-44D3-8D3C-C47398638233	2	5

Regards,

Waqas

[Anonymous]

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

[Zubair_Muhammad · 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 )
)

[Anonymous]

Hi,

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

Regards,

Waqas