Speed DAX Measure

[Anonymous]

I currently have a calculate divide function with several filters applied however it's taking a very long time to load. Is there any way to improve this?

DIVIDE(

CALCULATE(

COUNT('Intake_Data'[Company_Id]),

FILTER('Intake_Data','Intake_Data'[Intake_Data_Custom_1] in {"COP1","COP2"}),

FILTER('Intake_Data','Intake_Data'[Intake_Data_Grade] in {"E2","E3","E3L","R2","R3","R3L","U2","U3","U3L"})

),

CALCULATE(

COUNT('Intake_Data'[Company_Id]),

FILTER('Intake_Data','Intake_Data'[Intake_Data_Custom_1] in {"COP1","COP2"}),

ALLSELECTED() )

)

[harshnathani]

@Anonymous ,

VAR a =
    CALCULATE (
        COUNT ( 'Intake_Data'[Company_Id] ),
        FILTER (
            'Intake_Data',
            'Intake_Data'[Intake_Data_Custom_1]
                IN {
                "COP1",
                "COP2"
            }
                && 'Intake_Data'[Intake_Data_Grade]
                IN {
                "E2",
                "E3",
                "E3L",
                "R2",
                "R3",
                "R3L",
                "U2",
                "U3",
                "U3L"
            }
        )
    )
VAR b =
    CALCULATE (
        COUNT ( 'Intake_Data'[Company_Id] ),
        FILTER (
            'Intake_Data',
            'Intake_Data'[Intake_Data_Custom_1]
                IN {
                "COP1",
                "COP2"
            }
        )
    )
RETURN
    DIVIDE (
        a,
        b
    )

Regards,

Harsh Nathani

[Anonymous]

@harshnathani 

Here is what I have written:

VAR a =

CALCULATE (

COUNT ( 'looker_views lkr_Intake_Data'[Company_Id]),

FILTER (

'looker_views lkr_Intake_Data',

'looker_views lkr_Intake_Data'[Intake_Data_Custom_1]

IN {

"COP1",

"COP2"

}

&& 'looker_views lkr_Intake_Data'[Intake_Data_Grade]

IN {

"E2",

"E3",

"E3L",

"R2",

"R3",

"R3L",

"U2",

"U3",

"U3L"

}

)

)

VAR b =

CALCULATE (

COUNT ( 'looker_views lkr_Intake_Data'[Company_Id] ),

FILTER (

'looker_views lkr_Intake_Data',

'looker_views lkr_Intake_Data'[Intake_Data_Custom_1]

IN {

"COP1",

"COP2"

}

)

)

RETURN

DIVIDE (

a,

b

)

However its giving me an error:

The syntax for 'VAR' is incorrect. (DAX( CALCULATE ( COUNT ( 'looker_views lkr_Intake_Data'[Company_Id]), FILTER ( 'looker_views lkr_Intake_Data', 'looker_views lkr_Intake_Data'[Intake_Data_Custom_1] IN { "COP1", "COP2" } && 'looker_views lkr_Intake_Data'[Intake_Data_Grade] IN { "E2", "E3", "E3L", "R2", "R3", "R3L", "U2", "U3", "U3L" } ) )VAR b = CALCULATE ( COUNT ( 'looker_views lkr_Intake_Data'[Company_Id] ), FILTER ( 'looker_views lkr_Intake_Data', 'looker_views lkr_Intake_Data'[Intake_Data_Custom_1] IN { "COP1", "COP2" } ) )RETURN DIVIDE ( a, b ))).

[harshnathani · Accepted solution]

@Anonymous ,

I think some syntax error.

Please use this measure.

Measure =

VAR a =
CALCULATE (
COUNT ( 'looker_views lkr_Intake_Data'[Company_Id] ),
FILTER (
'looker_views lkr_Intake_Data',
'looker_views lkr_Intake_Data'[Intake_Data_Custom_1]
IN {
"COP1",
"COP2"
}
&& 'looker_views lkr_Intake_Data'[Intake_Data_Grade]
IN {
"E2",
"E3",
"E3L",
"R2",
"R3",
"R3L",
"U2",
"U3",
"U3L"
}
)
)

VAR b =
CALCULATE (
COUNT ( 'looker_views lkr_Intake_Data'[Company_Id] ),
FILTER (
'looker_views lkr_Intake_Data',
'looker_views lkr_Intake_Data'[Intake_Data_Custom_1]
IN {
"COP1",
"COP2"
}
)
)

RETURN
DIVIDE (
a,
b
)