FILTER multiple tables

[Kukusiki83]

Hello,

I need help!!!

I have a table - 'Calender'

second table - 'sales'

Third table - 'Tasks'

I want to calculate a distinct number of days (from the 'Calender') where:

'Sales'  - FILTER(SALES, SALES[Appels IN] >1 && SALES[email2] = [CurrentUser]

'Tasks' -  FILTER(Task, Task[email2] = NAMES[CurrentUser] && Task[hour] < 20 && Task[ALL TASK] >= 1

my formula looks like:

calculate(DISTINCTCOUNT(Calendar[Date]), FILTER(Task, Task[email2] = NAMES[CurrentUser] && Task[hour] < 20 && Task[ALL TASK] >= 1))

or

calculate(DISTINCTCOUNT(Calendar[Date]), FILTER(SALES, SALES[Appels IN] >1 && SALES[email2] = [CurrentUser]

both formulas work individualy, but I need to merge them in one:

calculate(DISTINCTCOUNT(Calendar[Date]), FILTER(SALES, SALES[Appels IN] >1 && SALES[email2] = [CurrentUser]) , FILTER(Task, Task[email2] = NAMES[CurrentUser] && Task[hour] < 20 && Task[ALL TASK] >= 1))

this didn't work.

thank you!

[parry2k]

@Kukusiki83 could you throw some sample data with the expected output? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Kukusiki83]

Hi, 

thanx for your reply. here's my tables and a wanted output

[v-cgao-msft · Accepted solution]

Hi @Kukusiki83 ,

Does the model look like this?

Try this measure and tell me the result.

Measure =
CALCULATE (
    DISTINCTCOUNT ( Calendar[Date] ),
    FILTER ( SALES, SALES[Appels IN] > 1 && SALES[email2] = [CurrentUser] ),
    CROSSFILTER ( Calendar[Date], Sales[Date], BOTH ),
    FILTER (
        Task,
        Task[email2] = [CurrentUser]
            && Task[hour] < 20
            && Task[ALL TASK] >= 1
    ),
    CROSSFILTER ( Calendar[Date], 'Task'[Date], BOTH )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group