Calculated Measure for multiple filters

Question

I am trying to total the number of days when a person is absent for both the am and the pm (on the same day) over a period of time based on multiple filters using a number of slicers.&nbsp;&nbsp;In essence&nbsp;Person#&nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Period&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Abs Type&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pm&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/2/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/3/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; personal&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/3/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pm&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; personal&nbsp;&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/3/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pm&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; personal&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/4/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/5/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/8/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/8/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pm&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/8/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pm&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/9/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pm&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/10/2019&nbsp; &nbsp; &nbsp; &nbsp;pm&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp;&nbsp;I can create a measure to count either the "am" or the "pm" but not both (aka 'and' or '&amp;&amp;').&nbsp;&nbsp;&nbsp;For example, I can create a measure to count the morning absences.&nbsp;&nbsp;&nbsp;#absent = CALCULATE (DISTINCTCOUNT ( BADetail[Person] ),Filter(BADetail, BADetail[SchoolPeriod] ="AM" ))&nbsp;&nbsp;I can then get the following chart to show&nbsp;&nbsp;Person#&nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Period&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Abs Type&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#absent&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/2/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/3/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; personal&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/4/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/5/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/8/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;am&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sick&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;___________________________________________________________________&nbsp;Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.0&nbsp;So I created a new measure&nbsp;#total for Date =&nbsp;SUMX(BADetail, BADetail[# absent])&nbsp;And this will show a correct count of 6.0 for just the 'am' absences, but not for the full days (am and pm)&nbsp;I have tried to alter my first measure&nbsp;#absent = CALCULATE (DISTINCTCOUNT ( BADetail[Person] ),Filter(BADetail, BADetail[SchoolPeriod] ="AM" &amp;&amp;&nbsp;BADetail[SchoolPeriod] ="PM"&nbsp;))&nbsp;And there is an error code.I had thought about alternative ways of doing this, I can't figure out why this does not work?&nbsp;&nbsp;&nbsp;Through the process I'd like to be able to create the summary chart below&nbsp; &nbsp;The correct answer for full absences is 3.0&nbsp;Person#&nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #absent&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/3/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/8/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp;&nbsp;Eventually, I'd like to find a way to note that some people were off full day, but used two different reasons.&nbsp; This will be another day.&nbsp;&nbsp;&nbsp;&nbsp;Much appreciated for any thoughts?&nbsp;Thanks again&nbsp;&nbsp;&nbsp;&nbsp;

sturlaws · Answer

Hi,&nbsp;SO,&nbsp;could you try this dax code:Measure =
VAR _am =
    CALCULATETABLE (
        VALUES ( 'Table'[Person#] );
        FILTER ( 'Table'; 'Table'[Period] = "am" )
    )
VAR _pm =
    CALCULATETABLE (
        VALUES ( 'Table'[Person#] );
        FILTER ( 'Table'; 'Table'[Period] = "pm" )
    )
RETURN
    COUNTROWS ( INTERSECT ( _am; _pm ) )&nbsp;Cheers,SturlaIf this post helps, then please consider Accepting it as the solution. Kudos are nice too.

so · Answer

Dear Strula,&nbsp;&nbsp;First, I want to thank you very much for responding and being so helpful!&nbsp;I tried to enter in your code into the measure and I noticed that I needed to convert the ";" to ","s for the code to work.&nbsp;When I did this, the measure produced a count of 1.&nbsp; I don't think the commas vs semicolons would make a difference in the outcome though?&nbsp;Second, I should have added that I was looking to count only those days where a person was absent for both the AM and the PM and ignore the days where they were absent in either of the AM or the PM.&nbsp;&nbsp;&nbsp;Again, thank you for your input and efforts.&nbsp;&nbsp;Much appreciated.&nbsp;&nbsp;&nbsp;

Forum Discussion

Calculated Measure for multiple filters

2 Replies

Related content

DAX Calculated Measure

Using Visual Calculation on a Calculated Group with Multiple Calculated Items

DAX Measure for Ranking the valid rows and calculating the average rating

CALCULATE with multiple filters

Multiple measures in matrix by year