topic Re: Summarize 2 different tables with filters and returning null for empty results in DAX Commands and Tips

Summarize 2 different tables with filters and returning null for empty results

Anonymous — Wed, 20 Sep 2023 14:56:00 GMT

Hi,

How is it possible to summarize together 2 different tables, excluding some values based on the results of a column?

For the following scenario:

I have a Table1 with unique IDs:

table1
ID
1
2
3
4
5
6

There is also this Table2 with some IDs from Table1:

table2
ID	reason
1	conclude
1	steady
1	not okay
2	steady
2	conclude
3	conclude
6	away

I would like to combine both tables into a Table3, where I have all ID's from Table1, and all the reasons from Table2, excluding the reason "conclude", also, when there is no reason for the IDs, the result will be "null":

note: for ID 3, there is only "conclude", so the result would be null, because I need to exclude when the reason is "conclude"

table3
ID	reason
1	steady
1	not okay
2	steady
3	null
4	null
5	null
6	away

I have thousands of reasons, so it would be easier to have a filter that excludes only when the reason is <> "conclude".

Thx!

Re: Summarize 2 different tables with filters and returning null for empty results

SQL_SousChef — Wed, 20 Sep 2023 16:13:05 GMT

Hello,

In Power Query select 'Merge Queries As New' to create you Table3. Before you Left Outer join them together remove "conclude" from Table2. The Results will give you the following once you expand the Table2 Results.

Results:

Thank you,

Re: Summarize 2 different tables with filters and returning null for empty results

tamerj1 — Wed, 20 Sep 2023 16:19:16 GMT

Hi Anonymous

I hope this works. Otherwise, we can also use GENERATE function.

Table3 =
NATURALLEFTOUTERJOIN (
SELECTCOLUMNS ( Table1, "ID", Table1[ID] & "" ),
SELECTCOLUMNS (
FILTER ( Table2, Table2[Reason] <> "Conclude" ),
"ID", Table2[ID] & "",
"Reason", Table2[Reason]
)
)

Re: Summarize 2 different tables with filters and returning null for empty results

ThxAlot — Wed, 20 Sep 2023 18:37:32 GMT