Forum Discussion

Nepal101's avatar
Nepal101
Icon for Helper III rankHelper III
4 years ago
Solved

Distinct count with two filter from different table (DAX)

Hello,  I am new to Dax and I need some help in getting an optimized way to create these measures. When I use these measures it takes a longer time to get the result  First measure=CALCULATE(     ...
Need Help
Show and Tell
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    See if it help to filter columns rather than tables.

     

    DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        FILTER (
            VALUES ( 'dimInspection'[InspectionLevelNumber] ),
            dimInspection[InspectionLevelNumber] IN { 3, 2, 1 }
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        FILTER (
            VALUES ( factInspectionViolation[IsOutOfService] ),
            factInspectionViolation[IsOutOfService] = "Y"
        ),
        FILTER (
            VALUES ( dimViolation[BASIC] ),
            dimViolation[BASIC]
                IN {
                "Driver Fitness",
                "Hours of Service Compliance",
                "Controlled Substances / Alcohol"
            }
        )
    )
)

     

     

    It's a bit cleaner-looking like this:

     

    DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        KEEPFILTERS ( dimInspection[InspectionLevelNumber] IN { 3, 2, 1 } )
    ),
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        KEEPFILTERS ( factInspectionViolation[IsOutOfService] = "Y" ),
        KEEPFILTERS ( dimViolation[BASIC]
            IN {
            "Driver Fitness",
            "Hours of Service Compliance",
            "Controlled Substances / Alcohol"
        } )
    )
)

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

See if it help to filter columns rather than tables.

 

DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        FILTER (
            VALUES ( 'dimInspection'[InspectionLevelNumber] ),
            dimInspection[InspectionLevelNumber] IN { 3, 2, 1 }
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        FILTER (
            VALUES ( factInspectionViolation[IsOutOfService] ),
            factInspectionViolation[IsOutOfService] = "Y"
        ),
        FILTER (
            VALUES ( dimViolation[BASIC] ),
            dimViolation[BASIC]
                IN {
                "Driver Fitness",
                "Hours of Service Compliance",
                "Controlled Substances / Alcohol"
            }
        )
    )
)

 

 

It's a bit cleaner-looking like this:

 

DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        KEEPFILTERS ( dimInspection[InspectionLevelNumber] IN { 3, 2, 1 } )
    ),
    CALCULATE (
        DISTINCTCOUNT ( factInspectionViolation[InspectionKey] ),
        KEEPFILTERS ( factInspectionViolation[IsOutOfService] = "Y" ),
        KEEPFILTERS ( dimViolation[BASIC]
            IN {
            "Driver Fitness",
            "Hours of Service Compliance",
            "Controlled Substances / Alcohol"
        } )
    )
)