Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.