cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vyacheslavg Helper II
Helper II

Union filter in Power Query or DAX

Hello,

I have a very simple filtering task (conceptually).

But can't figure out how to apply combined filter to a very simple dataset.

 

Original dataset

 

Item                                       Added by

1A
1B
1B
1C
2AA
2A A
3ABC
3B-
4A

 

What I want (result) is all items, which were added by A & B & C. 

 

Item                                             Added by

1A
1B
1B
1C

 

However, I'm getting below if I apply filters in Excel or in Power Query.

I don't need the item 4, because it was added only by A, not by A & B & C.

 

Item                                      Added by

1A
1B
1B
1C
4A

 

Is it possible to solve this using Power Query? Or I need to use DAX?

Thanks for your help and any suggestions.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Union filter in Power Query or DAX

What about something like this?

Calc Table.png

So Items 1 and 6 have all ABC, but item 3 does not since ABC happened in one row, but if that needs to be accounted for can change the code easily. 

ALL ABC Check = 
var __CurrentID= Table1[Item]
RETURN

Var __NewColumn=
CALCULATE(
    CONCATENATEX(
        'Table1',
        'Table1'[Added by]),
        FILTER(
            'Table1',
            Table1[Item] = __CurrentID
        )
)
RETURN

IF(
    AND(
        LEN(Table1[Added by]) =1,
        SEARCH("A",__NewColumn,,0)+SEARCH("B",__NewColumn,,0)+SEARCH("c",__NewColumn,,0) >= 3
    ),
    "ALL ABC",
    "Missing"
)

View solution in original post

5 REPLIES 5
vyacheslavg Helper II
Helper II

Re: Union filter in Power Query or DAX

This is my Power Query solution, quite clumsy.

I think this has some elegant solution in DAX.

 

Filtered rows to A, B and C in column "Added by".

Kept duplicates in "Item" column

 

This is a quite acceptable solution, but all my attempts with DAX (filter, calculatetable, etc.) - no success.

 

 

 

 

Anonymous
Not applicable

Re: Union filter in Power Query or DAX

I attached the pbix file below, but here's the final table:

Final Table.png

 

The applied steps will show how this was accomplished

 

File:

https://1drv.ms/u/s!Amqd8ArUSwDS3SWRX9IyTOty4ixq?e=8tFmPN

Highlighted
vyacheslavg Helper II
Helper II

Re: Union filter in Power Query or DAX

Thanks, however I'm afraid that this is not a solution I'm looking for.

This is a full code, which does exactly what I want.

But I still want to do it in DAX, but still can't figure out how.

I think that DAX solution would be much more elegant.

 

 

let
    Source = Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Added by] = "A" or [Added by] = "B" or [Added by] = "C")),
    #"Kept Duplicates" = let columnNames = {"Item"}, addCount = Table.Group(#"Filtered Rows", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Filtered Rows", columnNames, removeCount, columnNames, JoinKind.Inner)
in
    #"Kept Duplicates"

 

 

This is Excel file

https://www.dropbox.com/s/lyi4sfhz9pihwzz/multiple_filter_union_set_analysis.xlsx?dl=0

 

This is Power BI link with Power Query solution

 

https://www.dropbox.com/s/qgiz3qxbeq6d74q/combined%20complex%20filter.pbix?dl=0

 

 

 

 

 

Anonymous
Not applicable

Re: Union filter in Power Query or DAX

What about something like this?

Calc Table.png

So Items 1 and 6 have all ABC, but item 3 does not since ABC happened in one row, but if that needs to be accounted for can change the code easily. 

ALL ABC Check = 
var __CurrentID= Table1[Item]
RETURN

Var __NewColumn=
CALCULATE(
    CONCATENATEX(
        'Table1',
        'Table1'[Added by]),
        FILTER(
            'Table1',
            Table1[Item] = __CurrentID
        )
)
RETURN

IF(
    AND(
        LEN(Table1[Added by]) =1,
        SEARCH("A",__NewColumn,,0)+SEARCH("B",__NewColumn,,0)+SEARCH("c",__NewColumn,,0) >= 3
    ),
    "ALL ABC",
    "Missing"
)

View solution in original post

vyacheslavg Helper II
Helper II

Re: Union filter in Power Query or DAX

I will not pretend that I fully understand this DAX, but it works perfectly and I can easily modify it for production cases.

This is good, since I do not need to reload rather big sources and filter it in a fraction of second, unlike my Power Query or Pandas solutions. 

Thanks!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors