Thanks Thanks:  0
Likes Likes:  0
Results 1 to 1 of 1

Thread: DAX - OR Statement Required

  1. #1
    Board Regular AndrewKent's Avatar
    Join Date
    Jul 2006
    Location
    Edinburgh
    Posts
    887
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DAX - OR Statement Required


    DAX - OR Statement Required
    Hi all,

    I have a rather nifty Dax calculation that gives me everything I need however I need to update it so that it looks at two values in one column than just one. The DAX below Works however there must be a simpler way to do this;

    Code:
    Standard 12MR Q018 Positive :=
    CALCULATE (
        COUNTA ( tbl_data_diversity_leavers_survey[Employee ID] ),
        FILTER (
            ALLEXCEPT (
                tbl_data_diversity_leavers_survey,
                tbl_departments_matrix[WL5 Area],
                tbl_departments_matrix[Functional Area],
                tbl_departments_matrix[Business Area],
                tbl_data_diversity_leavers_survey[Gender],
                tbl_data_diversity_leavers_survey[Work Level],
                tbl_data_diversity_leavers_survey[Overall Area]
            ),
            (
                tbl_data_diversity_leavers_survey[Month ID]
                    >= MAX ( tbl_date_matrix[Month ID] ) - 11
            )
                && (
                    tbl_data_diversity_leavers_survey[Month ID] <= MAX ( tbl_date_matrix[Month ID] )
                )
        ),
        FILTER (
            ALLEXCEPT (
                tbl_data_diversity_leavers_survey,
                tbl_departments_matrix[WL5 Area],
                tbl_departments_matrix[Functional Area],
                tbl_departments_matrix[Business Area],
                tbl_data_diversity_leavers_survey[Gender],
                tbl_data_diversity_leavers_survey[Work Level],
                tbl_data_diversity_leavers_survey[Overall Area]
            ),
            tbl_data_diversity_leavers_survey[Question ID] = "Q018"
                && tbl_data_diversity_leavers_survey[Survey Completed] = "Completed"
                && tbl_data_diversity_leavers_survey[Answer] = "Strongly Agree"
        )
    )
        + CALCULATE (
            COUNTA ( tbl_data_diversity_leavers_survey[Employee ID] ),
            FILTER (
                ALLEXCEPT (
                    tbl_data_diversity_leavers_survey,
                    tbl_departments_matrix[WL5 Area],
                    tbl_departments_matrix[Functional Area],
                    tbl_departments_matrix[Business Area],
                    tbl_data_diversity_leavers_survey[Gender],
                    tbl_data_diversity_leavers_survey[Work Level],
                    tbl_data_diversity_leavers_survey[Overall Area]
                ),
                (
                    tbl_data_diversity_leavers_survey[Month ID]
                        >= MAX ( tbl_date_matrix[Month ID] ) - 11
                )
                    && (
                        tbl_data_diversity_leavers_survey[Month ID] <= MAX ( tbl_date_matrix[Month ID] )
                    )
            ),
            FILTER (
                ALLEXCEPT (
                    tbl_data_diversity_leavers_survey,
                    tbl_departments_matrix[WL5 Area],
                    tbl_departments_matrix[Functional Area],
                    tbl_departments_matrix[Business Area],
                    tbl_data_diversity_leavers_survey[Gender],
                    tbl_data_diversity_leavers_survey[Work Level],
                    tbl_data_diversity_leavers_survey[Overall Area]
                ),
                tbl_data_diversity_leavers_survey[Question ID] = "Q018"
                    && tbl_data_diversity_leavers_survey[Survey Completed] = "Completed"
                    && tbl_data_diversity_leavers_survey[Answer] = "Agree"
            )
        )
    Looking for an total of all records that have Question ID AND Survey Completed and either Agree or Strongly Agree in Answer. If that makes sense.

    Thanks in advance!

    Andy
    Last edited by AndrewKent; Oct 12th, 2017 at 10:11 AM.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com