Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing Two Different Datasets for missing values

Hi,   I have two different datasets/tables in Power BI. One that is a Power BI dataset and the other is a Google BigQuery direct query.   Lets call them table_a and table_b. I would like to compa...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    You can create a new calculated table 

    Missing From b =
EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) )

    Or you can create a measure, for example for a card visual 

    Missing From b =
CONCATENATEX (
    EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) ),
    [Column A],
    UNICHAR ( 10 )
)
rsbin's avatar
rsbin
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous ,

I have never seen this error message before.  I did a quick google search using the phrase "cannot be pushed to the remote data source and cannot be used in this scenario".

Are you using DirectQuery or a composite model?

If this is the case, we may have to try something different.  Suggest you search the same and to see if you can find an answer.  I am stumped at the moment.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Yeah unfortunately it is direct query so I will have to do some other searching, thanks for your help though!

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous ,

    May not be the cleanest solution, but are you able to create a new Table summarizing on

    'CMDB_Active[ip_address].  Then add your new column to this new calculated table.

    Due to the size of your dataset, it might degrade the performance of your model, but at least you can see if it gets you the results you are looking for.  Then work on the performance if it is an issue.