DAX Statement Problem

[OR0159236]

This is my Power BI canvas for a report I'm putting together. Let me try and explain the details of what I'm trying to accomplish and what my issue is.

1.  I have a dataset of INTAKES that is approximately 7k rows.
2. I have a INTAKE STATUS of "Pening Review/Assignment", "Under Investigation", "Closed", and "Investigation Complete."
3. I want to calculate a measure that give me the number of all INTAKES with an INVESTIGATION DUE DATE before 11/30/2021, inclusive, and with a INTAKE STATUS = "Pending Review/Assignment" or "Under Investigation."
   Here is my DAX measure:
   
   OVERDUE ALL INTAKES =
   CALCULATE(

   DISTINCTCOUNT(_NFSU_INTAKES_ALG[INTAKE ID]),

   ALL(_NFSU_INTAKES_ALG),

   FILTER(

   _NFSU_INTAKES_ALG, [INTAKE STATUS] in {"Pending Review/Assignment", "Under Investigation"} &&

   [INVESTIGATION DUE DATE] <= 11/30/2021 && [RECEIVED END DATE] >= 10/01/2019

   )

   )
   
   The problem here is the outcome is two (2). I know this is not correct since looking and the matrix below that number is definatly more than two.
   
   
4. I also want to calculate a measure of the same INTAKES population that are due as of 11/30/2021 and INTAKE STATUS = "Pending Review/Assignment" or "Under Investigation"; meaning it is not completed. Here is my DAX for this measure for this:
   
   OVERDUE REMAINING INTAKES =

   CALCULATE(

   DISTINCTCOUNT(_NFSU_INTAKES_ALG[INVESTID]),

   FILTER(_NFSU_INTAKES_ALG, _NFSU_INTAKES_ALG[INVESTIGATION DUE DATE] <= 11/30/2021 && ISBLANK([SURVEY EXIT DATE])),

   FILTER(_NFSU_INTAKES_ALG, _NFSU_INTAKES_ALG[INTAKE STATUS] in {"Pending Review/Assignment", "Under Investigation"})

   )

   
   This is giving me one (1) - not even close. I am expecting somewhere in the neighborhood of 834 INTAKES remaining.
   
   
5. What I want to do is determine the percentage of those INTAKES with a due date before 11/30/2021 that are completed to date. Knowing #3 and #4 I can do that but the number returned are not correct.

 

My DAX statement seem right, at least the syntax is, but obviously my logic is not. Can someone look at my DAX statements and maybe see something I'm not seeing.

Thank you in advance.

Vince

 

[OR0159236]

That worked. 🙂 Thank you.

[OR0159236]

Thank you, @amitchandak . This looks promising, the small change in the formatting gave me a more "likely" set of measures result. At least I don't get zeros (0), ones (1) or twos (2) for my measures. 🙂 Now the hard part is to validate the numbers I get with what is actual for a small sample with different variances in the filter.

[amitchandak · Accepted solution]

@OR0159236 , is it a format issue ?

Date like

OVERDUE ALL INTAKES =
CALCULATE(
DISTINCTCOUNT(_NFSU_INTAKES_ALG[INTAKE ID]),
ALL(_NFSU_INTAKES_ALG),
FILTER(
_NFSU_INTAKES_ALG, [INTAKE STATUS] in {"Pending Review/Assignment", "Under Investigation"} &&
[INVESTIGATION DUE DATE] <= date(2021,11,30) && [RECEIVED END DATE] >= date(2019,10,01)
)
)