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 date from a table unrelated to the ones with the interval dates.
the date to check is the minimum between yesterday and the max value of a date slicer (so if on that date slicer i select from 01/01/2023 to 31/12/2023 my date value will be 31/12/2023)

i tried to write it like: 

CalculatedColumn =
var today = today() - 1
var selected_date = max(Sales_DimDate[DDATE])
var min_date = min(today, selected_date)
return
    IF(
        min_date > Table[DATE_END],
        2, --Closed,
        IF(
            min_date < Table[DATE_START], 
            3, -- To Go,
            1 --Ongoing
        )
    )
but it freeze the max value of selected_date to the max of the table even if i filter it with a slicer

so i tried to not use variables (to avoid freezing values)
CalculatedColumn =
    IF(
        min(today() - 1, max(Sales_DimDate[DDATE])) > Table[DATE_END],
        2, --Closed,
        IF(
            min(today() - 1, max(Sales_DimDate[DDATE])) < Table[DATE_START], 
            3, -- To Go,
            1 --Ongoing
        )
    )

but it also freeze the value

so i tried to use a measure and call it in my syntax:
[measure] = min(today() - 1, max(Sales_DimDate[DDATE]))
CalculatedColumn =
    IF(
        [measure] > Table[DATE_END],
        2, --Closed,
        IF(
            [measure] < Table[DATE_START], 
            3, -- To Go,
            1 --Ongoing
        )
    )


but it still freeze the value when the slicer filters the Sales_DimDate table (changing its max value)
how can i do this? 
i feel really dumb not understanding a simple task like that
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.

     

3 Replies

    • 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?

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