Forum Discussion

Filippo91d's avatar
Filippo91d
New Member
1 year ago

problems with calculated column

hi, I apologize for my bad English.   I have 3 tables. In the first table there is a list of dates "_calendar", in the second "Tickets_DataApertura" and the third "Tickets_Datachiusura" I have a l...
Need Help
Tips and Tricks
Filippo91d's avatar
Filippo91d
New Member
1 year ago

ok, I corrected the type of the "date" field of the "_calendar" table with the type you indicated. Thanks!

 

exactly, they are both of these tables identical. the first one is linked to the "Date" field of the "_calendar" table with the "TKT_CreationDate - Date" field, while the second table is linked to the "Date" field of the "_calendar" table with the "TKT_ClosureDate - Date" field. I need this to find the number of tickets that have the creation date and the closing date within the same date filter. Thanks!

Deku's avatar
Deku
Super User
1 year ago

mark date table like this

 

Since you have the table dedicated to closed dates and that is filtered by date you can just count the number of rows

closed = countrows(Tickets_Datachiusura[TKT_ClosureDate - Date])

You could do this with a singe table if you used the date table as a role playing dimension, something to think about

 

  • Filippo91d's avatar
    Filippo91d
    New Member
    1 year ago

    how can I build the measure I need with the operation you indicated?

    I only need a measure that can set a flag to 1 if the tkt has the closing date within the range of dates selected in the date filter, otherwise set it to 0. obviously, in the table, only the tkts that also have the creation date within the range of dates selected must be displayed. 
    Example - date selected 2024-12-09 / 2024-12-15



     

    Thanks!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Filippo91d,

      Thank you for reaching out in Microsoft Community Forum.

      Thank you Deku  for the helpful response.

      Please use below DAX for the required output

      1.Create a measure for the flag

      TKT_ClosedInRange_Flag =
      VAR StartDate = MIN('_calendar'[Date])
      VAR EndDate = MAX('_calendar'[Date])
      RETURN
      IF (
      MAX('Tickets_DataApertura'[TKT_ClosureDate - Date]) >= StartDate &&
      MAX('Tickets_DataApertura'[TKT_ClosureDate - Date]) <= EndDate,
      1,
      0
      )


      2.Apply a visual-level filter

      TKT_OpenInRange_Flag =
      VAR StartDate = MIN('_calendar'[Date])
      VAR EndDate = MAX('_calendar'[Date])
      RETURN
      IF (
      MAX('Tickets_DataApertura'[TKT_CreationDate - Date]) >= StartDate &&
      MAX('Tickets_DataApertura'[TKT_CreationDate - Date]) <= EndDate,
      1,
      0
      )


      Please continue using Microsoft Community Forum.

      If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

      Regards,
      Pavan.