Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

2 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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)
  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.