Forum Discussion

Nepal101's avatar
Nepal101
Helper 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(

    DISTINCTCOUNT(factInspectionViolation[InspectionKey]),

    FILTER( 'dimInspection', dimInspection[InspectionLevelNumber] IN {3,2,1} )

    )
Second Measure =

CALCULATE(

    DISTINCTCOUNT(factInspectionViolation[InspectionKey]),

    FILTER ( factInspectionViolation, [IsOutOfService] = "Y" ),

    FILTER( dimViolation, [BASIC] IN { "Driver Fitness", "Hours of Service Compliance", "Controlled Substances / Alcohol"  }

))

Third Measure=DIVIDE([SecondMeasure],[First Measuer])

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"
        } )
    )
)

     

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super 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"
        } )
    )
)