topic Compare 2 Tables - with result in another table in DAX Commands and Tips

Compare 2 Tables - with result in another table

stefan321 — Fri, 24 May 2024 16:32:52 GMT

Hi,

the goal is:

Check "source Table 1" if

Mode= 'FULL' AND
CreatDate >= 8day AND
LastFULLBackup >= 8 days(or NULL)
then check result if Server and Instance combination can be found in "exclusion Table2" for exclusion.

Result should be shown in "result table3"

What is the best way to do it? Thanks a lot!

Example:

source Table1

Server	Instance	Database	Mode	CreateDate	LastFullBackup
ServerA	MSSQLSERVER	DB1	FULL	11/23/2022 13:14:00	03/22/2024 02:00:00
ServerA	MSSQLSERVER	DB2	FULL	05/23/2024 13:14:00
ServerA	MSSQLSERVER	DB3	SIMPLE	07/13/2023 13:14:00	05/22/2024 02:00:00
ServerA	Named1	DB4	FULL	09/24/2019 13:14:00	05/17/2024 13:14:00
ServerA	Named1	DB5	SIMPLE	05/22/2024 13:14:00	05/22/2024 13:14:00
ServerB	MSSQLSERVER	DB_test	FULL	07/13/2023 13:14:00	03/17/2024 13:14:00
ServerB	MSSQLSERVER	DB_1234	SIMPLE	07/21/2023 13:14:00	05/22/2024 13:14:00
ServerC	MSSQLSERVER	DB_1	SIMPLE	07/21/2023 13:14:00	05/22/2024 13:14:00

exclusion Table2

Server	Instance
ServerA	Named1
ServerC	MSSQLSERVER

result Table3

Server	Instance	Database	Mode	CreateDate	LastFullBackup
ServerA	MSSQLSERVER	DB1	FULL	11/23/2022 13:14:00	03/22/2024 02:00:00
ServerB	MSSQLSERVER	DB_test	FULL	07/13/2023 13:14:00	03/17/2024 13:14:00

Re: Compare 2 Tables - with result in another table

lbendlin — Thu, 23 May 2024 22:59:47 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Compare 2 Tables - with result in another table

stefan321 — Fri, 24 May 2024 13:58:14 GMT

Done.

Thanks.

Re: Compare 2 Tables - with result in another table

lbendlin — Fri, 24 May 2024 14:18:27 GMT

Why would ServerB be eligible? CreatedDate is not yesterday or today. Are these timestamps in UTC?

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBBC4IwFMDxryKehe29TURvWQbBjHLURSSCduxi0ufvOcN0LIMukz3cj/9W16E27dO0qyAIo7DU+qh0UZ2Lyu43OdC6PSlFHwCGgiFHDEBkIDPO7U+chtjPZcCRhsO8iUZ6DlsWPyyP36wcWZr+AgStelceVDE0JAwsIqZITztlDru/3s0NrCgnSSlDSecgdTRInNCvWjz2zTv8dR4v91z60plHN8n0X1osZXpZQCFnvXQell7TA6+98J9q8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, Instance = _t, Database = _t, Mode = _t, CreateDate = _t, LastFullBackup = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreateDate", type datetime}, {"LastFullBackup", type datetime}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Mode] = "FULL")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [CreateDate] > DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-1))), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [LastFullBackup] is null or [LastFullBackup] > DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-8))), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows2", {"Server", "Instance"}, exclusion, {"Server", "Instance"}, "exclusion", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "rows", each Table.RowCount([exclusion])), #"Filtered Rows3" = Table.SelectRows(#"Added Custom", each [rows] = 0) in #"Filtered Rows3"

Re: Compare 2 Tables - with result in another table

stefan321 — Fri, 24 May 2024 15:15:35 GMT

You mean why is ServerB reflecting in "result Table3", right?

CreatedDate from ServerB Databbase "DB_test" is 07/13/2023 13:14:00 and therefore older than 1 Day (CreatDate >= 1day). Same for LastFULLBackup.

Additionally ServerB is not in "exclusion Table2".

Re: Compare 2 Tables - with result in another table

lbendlin — Fri, 24 May 2024 15:37:57 GMT

therefore older than 1 Day (CreatDate >= 1day).

So you mean CreatedDate is less than yesterday?

Please restate your logic to reduce ambiguity.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBBC4IwFMDxryKehe29TURvWQbBjHLURSSCduxi0ufvOcN0LIMukz3cj/9W16E27dO0qyAIo7DU+qh0UZ2Lyu43OdC6PSlFHwCGgiFHDEBkIDPO7U+chtjPZcCRhsO8iUZ6DlsWPyyP36wcWZr+AgStelceVDE0JAwsIqZITztlDru/3s0NrCgnSSlDSecgdTRInNCvWjz2zTv8dR4v91z60plHN8n0X1osZXpZQCFnvXQell7TA6+98J9q8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, Instance = _t, Database = _t, Mode = _t, CreateDate = _t, LastFullBackup = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreateDate", type datetime}, {"LastFullBackup", type datetime}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Mode] = "FULL")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [CreateDate] < DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-1))), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [LastFullBackup] is null or [LastFullBackup] < DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-8))), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows2", {"Server", "Instance"}, exclusion, {"Server", "Instance"}, "exclusion", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "rows", each Table.RowCount([exclusion])), #"Filtered Rows3" = Table.SelectRows(#"Added Custom", each [rows] = 0) in #"Filtered Rows3"

Re: Compare 2 Tables - with result in another table

stefan321 — Fri, 24 May 2024 16:36:07 GMT

Yes correct.

I have increased the CreatedDate to >= 8 days but anything else is the correct logic in my point of view.

In simple words: i want to check if a Database Backup is too old.