Filter help on a 3 table scenerio

[Anonymous]

HI!

I am building a clustered bar and line chart based on data from two tables based on a common date.

The table 1 had Planned ID's and Finish weekending dates and table 2 has Completed IDs and weekending dates.

I created a thrid table in Power BI with a common weekending date and did a count from each of the above two tables to get the count by weekending dates. I want to build a table below the chart to display the individual Planned IDs which are not Completed as per the Table 2 information.

Table 1

A123       2020-06-14

A345       2020-07-19

B234        2020-08-23

Table 2

A987       2020-06-14

A345       2020-07-19

B421        2020-08-09

So basically in the table I am trying to create I want to show all the missing IDs from Table 2 for a particular date when the chart that I created above is clicked on a certain date.

Help will be gretaly appreciated.

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to my understanding, you want to display the Planned ID which are not Completed based on the same selected Date in Slicer, right?

You could use the following formula:

findCompletedID =
LOOKUPVALUE (
    Table2[Completed ID],
    Table2[Weekending date], SELECTEDVALUE ( dateForSlicer[Finish weekending date] )
)

Measure =
VAR _sele =
    SELECTEDVALUE ( dateForSlicer[Finish weekending date] )
RETURN
    IF (
        SELECTEDVALUE ( Table1[Finish weekending date] ) = _sele
            && SELECTEDVALUE ( Table1[Planned ID] ) <> [findCompletedID],
        1,
        0
    )

My visualization looks like this:

Did I answer your question ? Please mark my reply as solution.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Fowmy]

@Anonymous 

I am not sure if you need a new table in the model created, if so create a table using the code below, it shows table1 data excluding the IDs that are not found in table 2.

Table3 = 
FILTER(
    Table1,
    NOT Table1[ID] IN VALUES(Table2[ID])
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[amitchandak]

@Anonymous , see if except can help

table3 = except(table1,table2)

https://docs.microsoft.com/en-us/dax/except-function-dax

also check

https://docs.microsoft.com/en-us/dax/intersect-function-dax

[Anonymous]

Thanks for your reply but I get this message .

Each table argument of 'EXCEPT' must have the same number of columns.

Sorry I forgot to mention they are two different tables with different number of columns