Forum Discussion
Omit values based on date within another table
Hi, new to Power Bi but have some SQL experience. Struggling to complete a task and wondered if anyone can help me please?
I have Table A which includes a date column (dd/mm/yyyy). It is a standalone table, no relationship to any other tables
I want to filter the contents of Table B whereby if it contains a row of data with a date inlcuded in Table A, it omits the row.
E.g.
Table A
| WeekCommencing | ReportMonth | Reason |
| 01/01/2023 | January | Christmas |
| 12/02/2023 | February | Half Term |
| 30/04/2023 | April | Bank Holiday |
Table B
| WeekCommencing | SiteID | StaffNo |
| 30/04/2023 | 10 | 5 |
| 07/05/2023 | 8 | 12 |
I would like Table B to omit the row where the date is also shown in Table A - therefore the 30/04/2023 row in this example
I can then add up the StaffNo row to get a sumtotal, excluding the weeks shown in Table A.
Hopefully this makes sense, please can anyone advise how I can achieve this?
I would assume I can make a calculated column using DAX to achieve this, just not sure which formulae to use.
BTW, Table B has thousands of rows of data which need to be filtered!
Many thanks 🙂
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Method1: Handle it in Power Query Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NtA3MjAyVtJRMjQAEqZKsTrRSqb65jBRC5CMkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekCommencing = _t, SiteID = _t, StaffNo = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WeekCommencing", type date}, {"SiteID", Int64.Type}, {"StaffNo", Int64.Type}}), #"FilteredTable" =Table.SelectRows( #"Changed Type" , each not List.Contains(#"Table A"[WeekCommencing], [WeekCommencing])) in #"FilteredTable"Method2: Create a calculated column and filter it out using visual-level filter
Omit = VAR _date = CALCULATE ( MAX ( 'Table A'[WeekCommencing] ), FILTER ( 'Table A', 'Table A'[WeekCommencing] = 'Table B(2)'[WeekCommencing] ) ) RETURN IF ( ISBLANK ( _date ), 1, 0)Best Regards
2 Replies
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Method1: Handle it in Power Query Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NtA3MjAyVtJRMjQAEqZKsTrRSqb65jBRC5CMkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WeekCommencing = _t, SiteID = _t, StaffNo = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WeekCommencing", type date}, {"SiteID", Int64.Type}, {"StaffNo", Int64.Type}}), #"FilteredTable" =Table.SelectRows( #"Changed Type" , each not List.Contains(#"Table A"[WeekCommencing], [WeekCommencing])) in #"FilteredTable"Method2: Create a calculated column and filter it out using visual-level filter
Omit = VAR _date = CALCULATE ( MAX ( 'Table A'[WeekCommencing] ), FILTER ( 'Table A', 'Table A'[WeekCommencing] = 'Table B(2)'[WeekCommencing] ) ) RETURN IF ( ISBLANK ( _date ), 1, 0)Best Regards