Forum Discussion

kristi_in_heels's avatar
kristi_in_heels
Helper II
2 years ago
Solved

If then DAX help

Hello,

 

I have a number of different sheets, with date columns. I am trying to create a card visual which will pull 2 relevant dates based on the project phase, and then a calculation between the two.

 

Result 1 (Card 1)

If 'All Projects' [ProjectPhaseDescription] = "execution", then return date listed in 'All Projects' [ExecutionStartDate_Actual]

or

If 'All Projects' [ProjectPhaseDescription] = "Prefeasibility", then return date listed in 'All Projects' [PrefeasibilityStartDate_Actual]

or

If 'All Projects' [ProjectPhaseDescription] = "Feasibility", then return date listed in 'All Projects' [FeasibilityStartDate_Actual]

 

Result 2 (Card 2)

If 'All Projects' [ProjectPhaseDescription] = "execution", then return date listed in 'BudgetDates' [EXE]

or

If 'All Projects' [ProjectPhaseDescription] = "Prefeasibility", then return date listed in 'BudgetDates' [PFS]

or

If 'All Projects' [ProjectPhaseDescription] = "Feasibility", then return date listed in 'BudgetDates' [FS]

 

Calculation (Card 3)

Result 2 - Result 1 = # Days

 

Any assistance would be appreciated

 

Need Help
  • devesh_gupta's avatar
    devesh_gupta
    2 years ago

    kristi_in_heels  You can create DAX measures which uses Switch function for the above mentioned conditions by you.

     

    Result 1 (Card 1):

    Result1 =
    VAR PhaseDescription = SELECTEDVALUE('All Projects'[ProjectPhaseDescription])
    RETURN
    SWITCH (
    TRUE (),
    PhaseDescription = "execution", SELECTEDVALUE('All Projects'[ExecutionStartDate_Actual]),
    PhaseDescription = "Prefeasibility", SELECTEDVALUE('All Projects'[PrefeasibilityStartDate_Actual]),
    PhaseDescription = "Feasibility", SELECTEDVALUE('All Projects'[FeasibilityStartDate_Actual]),
    BLANK ()
    )

     

    Result 2 (Card 2):

    Result2 =
    VAR PhaseDescription = SELECTEDVALUE('All Projects'[ProjectPhaseDescription])
    RETURN
    SWITCH (
    TRUE (),
    PhaseDescription = "execution", SELECTEDVALUE('BudgetDates'[EXE]),
    PhaseDescription = "Prefeasibility", SELECTEDVALUE('BudgetDates'[PFS]),
    PhaseDescription = "Feasibility", SELECTEDVALUE('BudgetDates'[FS]),
    BLANK ()
    )

     

    Calculation (Card 3):

    Calculation = [Result2] - [Result1]

     

     

    Try this if it helps.

     

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    2 years ago

    kristi_in_heels 

    Result 1 = SWITCH(TRUE(),

                                   All Projects [ProjectPhaseDescription] = "execution", All Projects' [ExecutionStartDate_Actual],

                                   All Projects [ProjectPhaseDescription] = "Prefeasibility",  'All Projects' [PrefeasibilityStartDate_Actual],

                                    'All Projects' [ProjectPhaseDescription] = "Feasibility", 

    'All Projects' [FeasibilityStartDate_Actual])

     

    Result 2 = SWITCH(TRUE(),

                                   All Projects [ProjectPhaseDescription] = "execution",'BudgetDates' [EXE],

                                   All Projects [ProjectPhaseDescription] = "Prefeasibility", 'BudgetDates' [PFS],

                                    'All Projects' [ProjectPhaseDescription] = "Feasibility",  'BudgetDates' [FS])

     

    CARD 3 = DATEDIFF(Result2, Result 1, DAYS)

     

     

    • kristi_in_heels's avatar
      kristi_in_heels
      Helper II
      2 years ago

      Thank you for taking the time to respond. I saw the other reply before yours and that worked perfectly so I didn't apply your option. I appreciate your help though.

  • devesh_gupta's avatar
    devesh_gupta
    Impactful Individual
    2 years ago

    kristi_in_heels  You can create DAX measures which uses Switch function for the above mentioned conditions by you.

     

    Result 1 (Card 1):

    Result1 =
    VAR PhaseDescription = SELECTEDVALUE('All Projects'[ProjectPhaseDescription])
    RETURN
    SWITCH (
    TRUE (),
    PhaseDescription = "execution", SELECTEDVALUE('All Projects'[ExecutionStartDate_Actual]),
    PhaseDescription = "Prefeasibility", SELECTEDVALUE('All Projects'[PrefeasibilityStartDate_Actual]),
    PhaseDescription = "Feasibility", SELECTEDVALUE('All Projects'[FeasibilityStartDate_Actual]),
    BLANK ()
    )

     

    Result 2 (Card 2):

    Result2 =
    VAR PhaseDescription = SELECTEDVALUE('All Projects'[ProjectPhaseDescription])
    RETURN
    SWITCH (
    TRUE (),
    PhaseDescription = "execution", SELECTEDVALUE('BudgetDates'[EXE]),
    PhaseDescription = "Prefeasibility", SELECTEDVALUE('BudgetDates'[PFS]),
    PhaseDescription = "Feasibility", SELECTEDVALUE('BudgetDates'[FS]),
    BLANK ()
    )

     

    Calculation (Card 3):

    Calculation = [Result2] - [Result1]

     

     

    Try this if it helps.

     

    • kristi_in_heels's avatar
      kristi_in_heels
      Helper II
      2 years ago

      Thank you, this worked perfectly. I really appreciate your assistance.