cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## DAX Statement Problem

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.

Vince

1 ACCEPTED SOLUTION
Super User

@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)
)
)

3 REPLIES 3
New Member

That worked. 🙂 Thank you.

New Member

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.

Super User

@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)
)
)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors