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

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

Highlighted
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.

Top Solution Authors
Top Kudoed Authors