Forum Discussion

vyacheslavg's avatar
vyacheslavg
Helper II
7 years ago
Solved

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                                 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    What about something like this?

    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"
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

What about something like this?

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's avatar
vyacheslavg
Helper II
7 years ago

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!