Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Speed DAX Measure

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() )

)

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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
    )

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      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 ))).
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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
        )