cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MIchri
Resolver I
Resolver I

If combination of values in same column then

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

NameID
Mike1
Mike2
Mike3
Mike4
Mike5
Frank1
Frank2
Frank3
Frank4

 

Thanks a lot!

Mikkel

1 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")



View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @MIchri ,


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
Super User IV
Super User IV

@MIchri , 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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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")



View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.