cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User

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"
)
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
Super User

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

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"
)
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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 110 members 1,588 guests
Please welcome our newest community members: