If combination of values in same column then

[Anonymous]

Hi guys, 

I have a table which contains names, ID and I woud like a new column / table with result depending on combinations on IDs.

If person has access to both ID 1 and 5 then High risk else Low risk. There I need a new column or table with four columns: Name, ID, ID2 and Risk. Eg. Name: "Mike", ID: "1", ID2: "5", Risk: "High risk". 

Table1

Name	ID
Mike	1
Mike	2
Mike	3
Mike	4
Mike	5
Frank	1
Frank	2
Frank	3
Frank	4

Thanks a lot!

Mikkel

[v-henryk-mstf]

Hi @Anonymous ,

Your requirement is that different Names have different combinations of combination IDs?

Different parameters correspond to different combination ID and use if nested statement, references is as follows:

New column = 
var _cnt1 = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[ID] in {1,5}))
var _cnt2 = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[ID] in {1,3,5}))
var _cnt3 = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[ID] in {1,3,5,8}))
return
if(_cnt1=2, "High risk",IF(_cnt2=3,"High risk",IF(_cnt3=4,"High risk","Low risk")))

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a new column like

New column =
var _cnt = calculate(distinctcount(Table[ID]), filter(Table, [Name] = earlier([Name]) && Table[ID] in {1,5}))
return
if(_cnt=2, "High risk" ,"Low risk")

[Anonymous · Accepted solution]

Hi @amitchandak ,

Perhaps I simplified my table in my post but I have 200,000 rows and multiple names with the combination of IDs to make a "High risk". E.g. currently its returning "High risk" even though the ID is not 18 and 81: 

Risk =

var _cnt =

CALCULATE(DISTINCTCOUNT('dm fct_Adgangskontrol'[Object_ID_Per]),FILTER('dm fct_Adgangskontrol','dm fct_Adgangskontrol'[UserGroupNames_Brugere] = EARLIER('dm fct_Adgangskontrol'[UserGroupNames_Brugere]) && 'dm fct_Adgangskontrol'[Object_ID_Per] in {18,81}))

RETURN

IF(_cnt = 2, "High Risk","Low risk")