cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ypaotroicn
New Member

Filtering to reverse filtering. What??

Hello community!

 

I have a bit of a complicated question so I will try to set up the scenario.

 

I have an Excel data table showing deal numbers, individuals working on the deal, and then the amount the deal is worth. Numerous people work on each individual deal, so there are multiple rows with the same deal numbers and amounts, but with different individuals listed. These individuals also work on multiple deal numbers, and they are not always the same. This data goes through thousands of lines.

 

My question is, if I were to look at an individual (John Smith) and all the deals that he's worked on, but then also want a list of the individuals who worked on that deal, is there an easy way to use PowerBI to show that information?

 

I can set it up so that I can see all the deals that John Smith worked on, but then I would have to write down each deal (which can be many), and then filter them individually, but that would take a while.

I know how to do this through vlookups and such, but I was just looking for a more streamlined process.

 

Any help would be great!

2 REPLIES 2
CheenuSing
Community Champion
Community Champion

Hi @ypaotroicn

 

Can you please share data model, sample data and the output you expect.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
MarcelBeug
Community Champion
Community Champion

The query below reads tables "Deals" and "SelectedName" (1 row with the selected name).

It filters the deals on the selected name.

The result is joined with all deals (nestedjoin).

A column is added with all individuals (<comma><space> separated).

The column with tables from the nested join is removed,

 

let
    Source = Excel.CurrentWorkbook(){[Name="Deals"]}[Content],
    Deals = Table.TransformColumnTypes(Source,{{"Deal", Int64.Type}, {"Individual", type text}, {"Amount", Int64.Type}}),
    Source2 = Excel.CurrentWorkbook(){[Name="SelectedName"]}[Content],
    SelectedName = List.Single(Source2[Name]),
    FilteredDeals = Table.SelectRows(Deals, each ([Individual] = SelectedName)),
    Merged = Table.NestedJoin(FilteredDeals,{"Deal"},Deals,{"Deal"},"NewColumn",JoinKind.LeftOuter),
    Individuals = Table.AddColumn(Merged, "Individuals", each Text.Combine([NewColumn][Individual],", ")),
    RemovedNewColumn = Table.RemoveColumns(Individuals,{"NewColumn"})
in
    RemovedNewColumn
Specializing in Power Query Formula Language (M)

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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors