Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    Try this as VAR R1

     

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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