Forum Discussion

msam86's avatar
msam86
Helper I
2 years ago
Solved

Dynamic column values based on selected date

Hi guys,
I just started with power bi and I need a help.
I have this data:

Name     Year           Actual year      Status
A             2023         2024                Down
B             2024          2024               Attention
C             2025          2024               Up
D             2026         2024                Up


I need to have a data parameter.

Scenario 1 - The current year should always bring the attention status. However, if I select the current year for 2026, names A,B,C should be down and name D should be attention.

Scenario 2 - If I select the current year to be 2025, names A,B should be down, name C should be attention and name D, up.


I don't have a dashboard yet.
Anyone can help me?

Thanks a lot.

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi msam86 - I have checked as per sample data shared, can you please use the below dax logic

     

    create a parameter with numeric range, and specify the year values from 2023 to 2026 with increment 1

     

     

    Dynamic Status =
    VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
    RETURN
    SWITCH(
        TRUE(),
        'Testingdata'[Year] < SelectedYear, "Down",
        'Testingdata'[Year] = SelectedYear, "Attention",
        'Testingdata'[Year] > SelectedYear, "Up",
        'Testingdata'[Status]  -- Default to the original status if no condition matches
    )
     Ensure the parameter and calculated column are correctly named and referenced in your DAX formula.Adjust the range of years in the parameter to match your dataset.Test the functionality by selecting different years in the slicer and verifying the output in your table visual.

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi rajendraongole1 ,thanks for the quick reply, I'll add further.

    Hi msam86 ,

    The Table data is shown below:

    Please follow these steps:

    1. Create a parameter with numeric range

     

    2.Use the following DAX expression to create a measure(Be careful not to create calculated columns)

     

    Measure = 
VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
RETURN
SWITCH(
    TRUE(),
    YEAR(MAX('Table'[YEAR])) < SelectedYear, "Down",
    YEAR(MAX('Table'[YEAR])) = SelectedYear, "Attention",
    YEAR(MAX('Table'[YEAR])) > SelectedYear, "Up",
    MAX('Table'[Status])  -- Default to the original status if no condition matches
)

     

    3.Final output

    Details can be found in the .pbix file. If the problem is solved, please mark rajendraongole1  and I as the solution.

     

     

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi msam86 - I have checked as per sample data shared, can you please use the below dax logic

     

    create a parameter with numeric range, and specify the year values from 2023 to 2026 with increment 1

     

     

    Dynamic Status =
    VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
    RETURN
    SWITCH(
        TRUE(),
        'Testingdata'[Year] < SelectedYear, "Down",
        'Testingdata'[Year] = SelectedYear, "Attention",
        'Testingdata'[Year] > SelectedYear, "Up",
        'Testingdata'[Status]  -- Default to the original status if no condition matches
    )
     Ensure the parameter and calculated column are correctly named and referenced in your DAX formula.Adjust the range of years in the parameter to match your dataset.Test the functionality by selecting different years in the slicer and verifying the output in your table visual.

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • msam86's avatar
    msam86
    Helper I
    2 years ago

    Hi rajendraongole1 thanks a lot for your help.

     

    I think a made a mistake. I received an error: 

    Expressions that generate variable data types cannot be used to define calculated columns.

     

    If you help me one more time, I'll appreciate.

     

    Regards

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi rajendraongole1 ,thanks for the quick reply, I'll add further.

    Hi msam86 ,

    The Table data is shown below:

    Please follow these steps:

    1. Create a parameter with numeric range

     

    2.Use the following DAX expression to create a measure(Be careful not to create calculated columns)

     

    Measure = 
VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
RETURN
SWITCH(
    TRUE(),
    YEAR(MAX('Table'[YEAR])) < SelectedYear, "Down",
    YEAR(MAX('Table'[YEAR])) = SelectedYear, "Attention",
    YEAR(MAX('Table'[YEAR])) > SelectedYear, "Up",
    MAX('Table'[Status])  -- Default to the original status if no condition matches
)

     

    3.Final output

    Details can be found in the .pbix file. If the problem is solved, please mark rajendraongole1  and I as the solution.