Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
4 years ago
Solved

Variable instead of IF Statement?

Hi,

 

Is it possible to alter this code and use variables instead?

 

 

Deferral WD's = 
if(Deferrals[Calcuation Method] = 1,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[Case Start],Deferrals[actualstart]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),
if(Deferrals[Calcuation Method] = 2,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[actualstart.1]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),
if(Deferrals[Calcuation Method] = 3,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[Case End]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),0)))

 

 

Deferral WD's =
if(Deferrals[Calcuation Method] = 1,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[Case Start],Deferrals[actualstart]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),
if(Deferrals[Calcuation Method] = 2,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[actualstart.1]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),
if(Deferrals[Calcuation Method] = 3,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[Case End]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),0)))

Or is this fine left as it is?

 

Thanks

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi ArchStanton ,

     

    Please try:

    Deferral WD's =
VAR _a =
    CALCULATE (
        COUNTROWS ( 'Date Dimension' ),
        DATESBETWEEN (
            'Date Dimension'[Date],
            Deferrals[Case Start],
            Deferrals[actualstart]
        ),
        'Date Dimension'[Is Working Day] = TRUE (),
        ALL ( 'Date Dimension' )
    )
VAR _b =
    CALCULATE (
        COUNTROWS ( 'Date Dimension' ),
        DATESBETWEEN (
            'Date Dimension'[Date],
            Deferrals[actualend],
            Deferrals[actualstart.1]
        ),
        'Date Dimension'[Is Working Day] = TRUE (),
        ALL ( 'Date Dimension' )
    )
VAR _c =
    CALCULATE (
        COUNTROWS ( 'Date Dimension' ),
        DATESBETWEEN (
            'Date Dimension'[Date],
            Deferrals[actualend],
            Deferrals[Case End]
        ),
        'Date Dimension'[Is Working Day] = TRUE (),
        ALL ( 'Date Dimension' )
    )
RETURN
    SWITCH (
        TRUE (),
        Deferrals[Calcuation Method] = 1, _a,
        Deferrals[Calcuation Method] = 2, _b,
        Deferrals[Calcuation Method] = 3, _c,
        0
    )

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    ArchStanton Sure:

    Deferral WD's = 
VAR __CalcMethod = Deferrals[Calculation Method]
RETURN
if(__CalcMethod = 1,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[Case Start],Deferrals[actualstart]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),
if(__CalcMethod = 2,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[actualstart.1]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),
if(__CalcMethod = 3,CALCULATE(
    COUNTROWS('Date Dimension'),
    DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[Case End]),'Date Dimension'[Is Working Day] = TRUE(),
    ALL('Date Dimension')
),0)))

    And if you do that, you could switch to a SWITCH statement instead of nested IF statements which would be way cleaner code.

    • ArchStanton's avatar
      ArchStanton
      Icon for Power Participant rankPower Participant
      4 years ago

      Hi, 

      That makes sense but I'm not 100% sure how to achieve this. Using previous examples I have something like this I know its incorrect:

       

      Deferral WD's =
      VAR __CalcMethod1 = CALCULATE(
      COUNTROWS('Date Dimension'),
      DATESBETWEEN('Date Dimension'[Date],Deferrals[Case Start],Deferrals[actualstart]),'Date Dimension'[Is Working Day] = TRUE(),
      ALL('Date Dimension')
      ),
      VAR__CalcMethod2 = CALCULATE(
      COUNTROWS('Date Dimension'),
      DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[actualstart.1]),'Date Dimension'[Is Working Day] = TRUE(),
      ALL('Date Dimension')
      ),
      VAR__CalcMethod = 3,CALCULATE(
      COUNTROWS('Date Dimension'),
      DATESBETWEEN('Date Dimension'[Date],Deferrals[actualend],Deferrals[Case End]),'Date Dimension'[Is Working Day] = TRUE(),
      ALL('Date Dimension')
      ),0)))


      RETURN
      SWITCH(
      TRUE,
      __CalcMethod1
      __CalcMethod2
      __CalcMethod3

      )

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi ArchStanton ,

     

    Please try:

    Deferral WD's =
VAR _a =
    CALCULATE (
        COUNTROWS ( 'Date Dimension' ),
        DATESBETWEEN (
            'Date Dimension'[Date],
            Deferrals[Case Start],
            Deferrals[actualstart]
        ),
        'Date Dimension'[Is Working Day] = TRUE (),
        ALL ( 'Date Dimension' )
    )
VAR _b =
    CALCULATE (
        COUNTROWS ( 'Date Dimension' ),
        DATESBETWEEN (
            'Date Dimension'[Date],
            Deferrals[actualend],
            Deferrals[actualstart.1]
        ),
        'Date Dimension'[Is Working Day] = TRUE (),
        ALL ( 'Date Dimension' )
    )
VAR _c =
    CALCULATE (
        COUNTROWS ( 'Date Dimension' ),
        DATESBETWEEN (
            'Date Dimension'[Date],
            Deferrals[actualend],
            Deferrals[Case End]
        ),
        'Date Dimension'[Is Working Day] = TRUE (),
        ALL ( 'Date Dimension' )
    )
RETURN
    SWITCH (
        TRUE (),
        Deferrals[Calcuation Method] = 1, _a,
        Deferrals[Calcuation Method] = 2, _b,
        Deferrals[Calcuation Method] = 3, _c,
        0
    )

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.