Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Filter with dates of two different tables

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
A01/02/2018
B 
C 

 

ZoneDate
A01/02/2019
B02/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

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

You could append queries to one query,

Capture9.JPG

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")

Capture10.JPG

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

You could append queries to one query,

Capture9.JPG

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")

Capture10.JPG

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
Community Champion
Community Champion

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)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.