Filter with dates of two different tables

[Anonymous]

Hello.

Apparently simple problem:

I have two tables. One for year 2018, another for year 2019, and I can link them by zone. 

Zone	Date
A	01/02/2018
B
C

Zone	Date
A	01/02/2019
B	02/02/2019
C

So, in this case, I want to know wich of my zones have no information (zone C) on the Date row, on both, the 2018 and 2019 tables.

I can filter get a filter with Year: 2018, 2019 and Null, but I can only get it to filter one of the tables, not both.

Thank you,
Carlos Soares

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You could append queries to one query,

Then create measures

countall = CALCULATE(COUNTA(Append1[Date]),ALLEXCEPT(Append1,Append1[Zone]))

countblank = CALCULATE(COUNTBLANK(Append1[Date]),ALLEXCEPT(Append1,Append1[Zone]))

isblank = IF([countall]-[countblank]=0,"no information","has infor")

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @Anonymous 

you can try a new calculated table, but its not a Power Query, its a DAX solution

Table = 
FILTER(
ADDCOLUMNS(
UNION(
DISTINCT(Table2018[Zone]),
DISTINCT(Table2019[Zone])
),
"Count",
CALCULATE(COUNTROWS(Table2018),Table2018[Zone]=EARLIER([Zone]),NOT(ISBLANK(Table2018[Date]))) + CALCULATE(COUNTROWS(Table2018),Table2019[Zone]=EARLIER([Zone]),NOT(ISBLANK(Table2019[Date])))
),
[Count] < 1)