Skip to main content
cancel
Showing results for 
Search instead 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

Reply
OR0159236
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.

DAX Problem.png 

 

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

1 ACCEPTED SOLUTION
amitchandak
Super User
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)
)
)

View solution in original post

3 REPLIES 3
OR0159236
New Member

That worked. 🙂 Thank you.

OR0159236
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.

 

amitchandak
Super User
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)
)
)

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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