cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vyacheslavg Regular Visitor
Regular Visitor

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
Super User I
Super User I

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 Regular Visitor
Regular Visitor

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.

 

 

 

 

Super User I
Super User I

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 Regular Visitor
Regular Visitor

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

 

 

 

 

 

Super User I
Super User I

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 Regular Visitor
Regular Visitor

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors