Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
1 | A |
1 | B |
1 | B |
1 | C |
2 | AA |
2 | A A |
3 | ABC |
3 | B- |
4 | A |
What I want (result) is all items, which were added by A & B & C.
Item Added by
1 | A |
1 | B |
1 | B |
1 | C |
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
1 | A |
1 | B |
1 | B |
1 | C |
4 | A |
Is it possible to solve this using Power Query? Or I need to use DAX?
Thanks for your help and any suggestions.
Solved! Go to Solution.
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" )
I attached the pbix file below, but here's the final table:
The applied steps will show how this was accomplished
File:
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
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" )
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!
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.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |