Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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.
@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")
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |