locked
"Calculate" and multiple OR filters for different tables RRS feed

  • Question

  • I need to count how many months an employee has been employed in a specific area, to do that I have used the following measure:

    Number of Months = Calculate(
    DISTINCTCOUNT('Calendar'[Month_name]);
    Filter(Facts;COUNTROWS(facts)>0)
    )

    Which works fine, it calculates how many months an employee has been working by counting the months in the calendar table and filtering the fact table to be sure that the employee has in fact been working.

    My problem is then, that some employees has accumulated a lot of vacation and there fore sometimes do not work in a month, but has a complete month of vacation, I therefore need to filter also the absence table.

    I then tried the following measure:

    Number of Months = Calculate(
    DISTINCTCOUNT('Calendar'[Month_name]);
    Filter(Facts;COUNTROWS(facts)>0);
    Filter(Absence;COUNTROWS(Absence)>0)
    )

    But that naturally doesn't work as both filters then has to be true at the same time (both work and vacation in same month)

    How do I get an "OR" statement in the filters when it is two different tables? I tried:

    Number of Months = Calculate(
    DISTINCTCOUNT('Calendar'[Month_name]);
    Filter(Facts;COUNTROWS(facts)>0) or
    Filter(Absence;COUNTROWS(Absence)>0)
    )

     and 

    Number of Months = Calculate(
    DISTINCTCOUNT('Calendar'[Month_name]);
    Filter(Facts;COUNTROWS(facts)>0) ||
    Filter(Absence;COUNTROWS(Absence)>0)
    )

    But none of those work. The first one just doesn't work and the second one states that I have used a true/false expression where it is not allowed.



    Wednesday, November 22, 2017 9:04 AM

Answers

  • You should be able to filter the distinct months and do a countrows over them.
    eg.

    Number of Months =
    COUNTROWS (
    FILTER( DISTINCT( 'Calendar'[Month_name] ), CALCULATE(COUNTROWS ( facts )) > 0 || CALCULATE( COUNTROWS ( Absence )) > 0 ) )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Morten_DK Thursday, November 23, 2017 7:49 PM
    Thursday, November 23, 2017 4:08 AM

All replies

  • Hi Morten_DK,

    Thanks for your question.

    Based on my understanding,to apply or to filters, you can try something like this:
    Number of Months =
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[Month_name] );
        OR (
            FILTER ( Facts; COUNTROWS ( facts ) > 0 );
            FILTER ( Absence; COUNTROWS ( Absence ) > 0 )
        )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, November 23, 2017 2:36 AM
  • You should be able to filter the distinct months and do a countrows over them.
    eg.

    Number of Months =
    COUNTROWS (
    FILTER( DISTINCT( 'Calendar'[Month_name] ), CALCULATE(COUNTROWS ( facts )) > 0 || CALCULATE( COUNTROWS ( Absence )) > 0 ) )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Morten_DK Thursday, November 23, 2017 7:49 PM
    Thursday, November 23, 2017 4:08 AM
  • Thanks the countrows and filter did the job.

    The "Or" condition before the two filters unfortunately also returned an error that true/false expression used where it was not allowed.

    Thursday, November 23, 2017 7:51 PM