Forum Discussion

Kukusiki83's avatar
Kukusiki83
Frequent Visitor
2 years ago
Solved

FILTER multiple tables

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!

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies