Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

calculated column changing with slicer changes

Hello! i probably have a really stupid issue. i have a table with a start date, and an end date i want to have a calculated column that populates "ongoing" "to go" "Closed" based on a selected dat...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    According to your statement, I think your requirement is to show results which are dynamic being impacted by Slicer.

    As far as I know, Power BI doesn't support us to show dynamic results in calculated columns.

    I suggest you to try measure which should meet your requirement. And you can show results in a visual.

    CalculatedColumn =
VAR today =
    TODAY () - 1
VAR selected_date =
    MAX ( Sales_DimDate[DDATE] )
VAR min_date =
    MIN ( today, selected_date )
RETURN
    IF (
        min_date > MAX ( Table[DATE_END] ),
        "Closed",
        IF ( min_date < MAX ( Table[DATE_START] ), "To Go", "On going" )
    )

     

    Best Regards,
    Rico Zhou

     

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

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi! Thank you for your response. I've watched the two videos but there are some differences that i want to point out to understand if those are the things that makes my model work differently.
the value in the video (terminated, hired, etc.) are found by a measure, not in a calculated column.
i need the calculated column because i need to do a slicer with " To Go", "Ongoing" etc. i don't think i can use those measures.
secondly there is a direct relationship between the dim date filtered and the table.
in my case i have a dim date related to the sale of a product and a table with the opening period of the store. those two are NOT related. and that is correct in my model, i have a "store_DimDate" in my model but the thing i need is to place my self in a date prior to today and see the situation of my sale and of my store status on that date.
do you think that this two differences are the things that makes my calculated column not work?