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,
I have 3 columns. 1st column is a group that consists of people who have a category of valid or not valid. I am trying to work out how I can make an additional column that will say if each group has atleast 1 valid member.
Group | Person | Valid | Group Has Valid Member |
Group 1 | Person 1 | Valid | Yes |
Group 1 | Person 2 | Not Valid | Yes |
Group 2 | Person 3 | Valid | Yes |
Group 2 | Person 4 | Valid | Yes |
Group 3 | Person 5 | Not Valid | No |
Group 4 | Person 6 | Not Valid | No |
Group 5 | Person 7 | Valid | Yes |
Group 5 | Person 8 | Not Valid | Yes |
Group 6 | Person 9 | Not Valid | No |
I'm not sure what to look for so any guidance greatly appreciated.
Many thanks
Solved! Go to Solution.
@NiugeS
Add the following Column to your table:
Valid Member =
IF(
CALCULATE(
COUNTROWS('Table'),
ALLEXCEPT('Table','Table'[Group]),
'Table'[Valid] = "Valid"
) > 0 ,
"Yes",
"No"
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@NiugeS - You could create a measure like this:
Has Valid Member =
VAR __Group = MAX('Table'[Group])
VAR __Table = FILTER('Table',[Group] = __Group && [Valid] = "Valid")
RETURN
IF(ISBLANK(__Table),"No","Yes")
As a column the same thing would be:
Has Valid Member =
VAR __Group = 'Table'[Group]
VAR __Table = FILTER('Table',[Group] = __Group && [Valid] = "Valid")
RETURN
IF(ISBLANK(__Table),"No","Yes")
@Fowmy @Greg_Deckler Thank you both for taking the time to respond. It appears I may have needed to provide you with more information.
The table is made up of two sources which has a lot of columns in both. Is there a way to get a simmilar result without merging the two sources?
Source 1 has Group and person and Source 2 has person and valid.
To avoid any confusion, i've attached a test pbix file. Is it possible to create a calculated column or measure in this scenario? Any help appreciated.
Thank you
@NiugeS
The Calculated column I provided should still work, can you check,
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Thank you. I got errors when I first tried but have since modified the calculated column you provided and modified it. I'm not confident the below is correct but appears to work. Wasn't sure which table to refer to.
Hi @NiugeS
Please add it to your "Table" table, as tested and analyzed, it shows a correct result and will get a correct result based on a bigger data.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It looks fine, you have relationship between the tables and the results are correct as mentioned.
can you verify and confirm?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@NiugeS
Add the following Column to your table:
Valid Member =
IF(
CALCULATE(
COUNTROWS('Table'),
ALLEXCEPT('Table','Table'[Group]),
'Table'[Valid] = "Valid"
) > 0 ,
"Yes",
"No"
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |