none
DAX Summing to include Blanks RRS feed

  • Question

  • Hello

    I’m using an old 2012 cube , that doesn’t have DateDiff support, 

    I’ve got a student table that has a Last_Exam_Date and  Next_Exam_Date attributes. I have written the measures below that calculates the difference between the 2 exam dates (Measure 1 and 2 are doing the same thing and return the same numbers). The issue is that when I add the measure, it only returns the students that have both a Last and Next Exam date. I want to return all students, because not all will have both Next and Last Dates.  

    In measure 3, I’m replacing Blanks with 0, but I end huge values, if one is blank

    I can include another measure, that I know will return all rows, or I could use Calc columns, but I want to use a measure here, and  I’m not sure how to total Cols and include Blank values. Any suggestions appreciated.

    Measure 1

    SUMX (

            Students,

            IF (

                NOT ( ISBLANK ( Students[Next_Exam_Date] ) )

                    && NOT ( ISBLANK ( Students[Last_Exam_Date] ) ),

                VALUE ( Students[Next_Exam_Date] ) - VALUE ( Students[Last_Exam_Date] )

            )

        )

    Measure 2

    SUMX (

            SUMMARIZE (

                Student,

                Student[StudentID],

                Student[Last_Exam_Date],

                Student[Next_Exam_Date],

                "DaysBetween",  IF (

                    NOT ( ISBLANK ( Student[Next_Exam_Date] ) )

                        && NOT ( ISBLANK ( Student[Last_Exam_Date] ) ),

                    VALUE ( Student[Next_Exam_Date] ) - VALUE ( Student[Last_Exam_Date] )

                )

            ),

            [DaysBetween]

        )

    Measure 3

    SUMX (

            Student,

            VALUE (

                IF (

                    NOT ( ISBLANK ( Student[Next_Exam_Date] ) ),

                    Student[Next_Exam_Date],

                    "0"

                )

            )

                - VALUE (

                    IF (

                        NOT ( ISBLANK ( Student[Last_Exam_Date] ) ),

                        Student[Last_Exam_Date],

                        "0"

                    )

                )

        )

    Friday, October 4, 2019 12:28 PM

Answers

  • Thanks to those who've viewed, I've got a solution that works ...

    ROW (

        "s", SUMX (

            SUMMARIZE (

                Student,

                Student[StudentID],

                Student[Last_Exam_Date],

                Student[Nexst_Exam_Date],

                "v_Last", IF (

                    ISBLANK ( Student[Nexst_Exam_Date] ) || ISBLANK ( Student[Last_Exam_Date] ),

                    0,

                    Student[Last_Exam_Date]

                ),

                "v_Next", IF (

                    ISBLANK ( Student[Nexst_Exam_Date] ) || ISBLANK ( Student[Last_Exam_Date] ),

                    0,

                    Student[Nexst_Exam_Date]

                )

            ),

            [v_next] - [v_Last]

        )

    )

    • Marked as answer by ryand09 Friday, October 4, 2019 7:40 PM
    Friday, October 4, 2019 7:40 PM