Forum Discussion
How to compare cell values in a table with column from another table to find unmatched values?
I'm using a table from Sharepoint list which stores documents uploaded for each month. Months are stored as 21-12-December, 22-01-January and so on. Say the year starts from 21-08-August and ends at 22-07-July and 10 branches upload a file for each month that has passed.
My report at the moment has a slicer to select the branch. When I select 01-Alpha branch in the slicer, I want a list that shows the months remaining for the branch like 21-11-November, 21-12-December, 22-01-January and so on. If I have table with a months list from 21-08-August to 22-07-July, how do I compare entries for each branch to find which months remain?
Here's how my data looks.
Hi tundra ,
If you want to implement it in PQ, please create a new query and paste the following code.
let Source = Table.SelectColumns(records,{"branch"}), #"Removed Duplicates" = Table.Distinct(Source), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "RecordsMonthsList", each let myfunction = (Currentbranch) => let SelectRows = Table.SelectRows(records, each [branch] = Currentbranch) in SelectRows, ListData = myfunction([branch])[#"month-of"] in ListData), #"Added Custom1" = Table.AddColumn(#"Added Custom", "unmatched months", each List.RemoveMatchingItems(months[#"months list"],[RecordsMonthsList])), #"Expanded unmatched months" = Table.ExpandListColumn(#"Added Custom1", "unmatched months"), #"Removed Columns" = Table.RemoveColumns(#"Expanded unmatched months",{"RecordsMonthsList"}) in #"Removed Columns"
If you want to implement it in DAX, please create the following inter-table relationship and measure, and use the measure as a visual level filter.Measure = COUNTROWS ( FILTER ( months, NOT months[months list] IN VALUES ( records[month-of] ) ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-kkf-msftCommunity Support
Hi tundra ,
If you want to implement it in PQ, please create a new query and paste the following code.
let Source = Table.SelectColumns(records,{"branch"}), #"Removed Duplicates" = Table.Distinct(Source), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "RecordsMonthsList", each let myfunction = (Currentbranch) => let SelectRows = Table.SelectRows(records, each [branch] = Currentbranch) in SelectRows, ListData = myfunction([branch])[#"month-of"] in ListData), #"Added Custom1" = Table.AddColumn(#"Added Custom", "unmatched months", each List.RemoveMatchingItems(months[#"months list"],[RecordsMonthsList])), #"Expanded unmatched months" = Table.ExpandListColumn(#"Added Custom1", "unmatched months"), #"Removed Columns" = Table.RemoveColumns(#"Expanded unmatched months",{"RecordsMonthsList"}) in #"Removed Columns"
If you want to implement it in DAX, please create the following inter-table relationship and measure, and use the measure as a visual level filter.Measure = COUNTROWS ( FILTER ( months, NOT months[months list] IN VALUES ( records[month-of] ) ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Used the DAX measure filter. It worked!
Many thanks! You're a lifesaver.
