cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors