Omit values based on date within another table

[Anonymous]

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 🙂

[Anonymous]

Many thanks for this, much appreciated. I'll give it a try today.  

[Anonymous · Accepted solution]

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