Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
philtab
Frequent Visitor

Item of a column matches to all values of second column

Hi all,

I have the following problem: I want to find the items in a column which match to all items in a second column.

 

Example: I want the users who match to all 3 products:

ProductUserResults
Product AUser1TRUE
Product BUser1TRUE
Product CUser1TRUE
Product AUser2FALSE
Product BUser2FALSE
Product AUser3TRUE
Product BUser3TRUE
Product CUser3TRUE
Product BUser4FALSE

 

Any help would be much appreciated.

Many thanks in advance

Philip

2 ACCEPTED SOLUTIONS
camargos88
Community Champion
Community Champion

@philtab ,

 

Check this calculated column:

 

Column = 
VAR _qtde = DISTINCTCOUNT('Table'[Product])
RETURN CALCULATE(DISTINCTCOUNT('Table'[Product]), FILTER(ALL('Table'), 'Table'[User] = EARLIER('Table'[User]))) = _qtde

 

 

Capture.PNG

 

Power Query:

let _user = [User] in
List.Count(List.Distinct(#"Changed Type"[Product])) = 
List.Count(List.Select(#"Changed Type"[User], each _ = _user))

 

Capture.PNG

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Many thanks 

You nailed it 🙂

View solution in original post

5 REPLIES 5
camargos88
Community Champion
Community Champion

@philtab ,

 

Check this calculated column:

 

Column = 
VAR _qtde = DISTINCTCOUNT('Table'[Product])
RETURN CALCULATE(DISTINCTCOUNT('Table'[Product]), FILTER(ALL('Table'), 'Table'[User] = EARLIER('Table'[User]))) = _qtde

 

 

Capture.PNG

 

Power Query:

let _user = [User] in
List.Count(List.Distinct(#"Changed Type"[Product])) = 
List.Count(List.Select(#"Changed Type"[User], each _ = _user))

 

Capture.PNG

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Many thanks 

You nailed it 🙂

Hi @philtab ,

 

I'm happy it helped.

Please accept my solution as an answer, so other users can benefit from it.

Thanks.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Hi 

 

CategoryProductUserResult
XProduct AUser1TRUE
XProduct BUser1TRUE
XProduct CUser1TRUE
XProduct BUser2FALSE
YProduct DUser3TRUE
YProduct EUser3TRUE
YProduct DUser4FALSE
YProduct EUser5FALSE
ZProduct FUser2TRUE
ZProduct GUser2TRUE
ZProduct FUser6FALSE

 

Is there are way to address this in your power query statement?

 

Many thanks again

Philip

 

@philtab ,

 

Try this new code:

let 
_user = [User],
_category = [Category]
in
List.Count(List.Distinct(Table.SelectRows(#"Changed Type", 
each [Category] = _category)[Product])) = 
List.Count(List.Distinct(Table.SelectRows(#"Changed Type", 
each [User] = _user and [Category] = _category)[Product]))


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors