Forum Discussion

lbendlin's avatar
lbendlin
Super User
1 year ago

Contrary to natural language, " I want a and b" is expressed in boolean logic via OR  (||), not AND (&&)

 

 

tmp = 
CALCULATE(
    COUNTROWS(tickets)
    tickets[status] IN {">Completed*",">Completed (No email)"},
    PREVIOUSMONTH('calendar'[date])
)

 

ajdm2007's avatar
ajdm2007
Helper III
1 year ago

Hello lbendlin ,

 

Thanks for your thought, I understand what you are saying.

 

Let's do it this way:

 

I have a formula that calculates the resolved tickets.

total_tickets_solved = 
COUNTROWS(
    FILTER(
        tickets,
        RELATED('status'[description]) = "Completed"
    )
)

This formula works smoothly as you can see.

 

Now my next step is to calculate the number of solved tickets for the previous month. So adding the metric that already works and invoking the previous month function, the formula would be this:

tmp = 
CALCULATE(
    [total_tickets_solved],
    PREVIOUSMONTH('calendar'[date])
)

 

But it does not work, the result returns blank.

So in my understanding this means that the problem is in the relationship between the ticket table and the calendar table.

 

Any suggestions to confirm what I think?

 

Thanks for any comments.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

     

    total_tickets_solved = 
COUNTROWS(
    FILTER(
        tickets,
        RELATED('status'[description]) = "Completed"
    )
)

     

    RELATED has no place in a measure. The relationship is implied through the data model.

     

     

    total_tickets_solved = 
CALCULATE(COUNTROWS(tickets),
    'status'[description] = "Completed"
)

     

    In addition you should try and avoid filtering entire tables. Filter columns when possible.

    • ajdm2007's avatar
      ajdm2007
      Helper III
      1 year ago

      This is my goal:

       

      I have already the Total Tickets Solved one.

      So, based on your last insight, what should the measure's formula be?

       

      Thank you so much for your support.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ajdm2007 ,

     

    Have you done any filtering on the calendar date column?

     

    You referenced the calendar date column in the PREVIOUSMONTH function.

     

    If not, the PREVIOUSMONTH function returns all dates in the previous month using the first date in the calendar date column as input. However, this returns the empty table.
    This causes the card visual to return blank.


    You can try adding the calendar date column to the slicer, select a date and see if the card visual changes.

     

    Best Regards,

    Dengliang Li

     

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

    • ajdm2007's avatar
      ajdm2007
      Helper III
      1 year ago

      Hello Anonymous ,

       

      Thanks for your input! I do have filters enabled, and there's data in the ticket table as well.

       

      I’m pretty sure the issue is with the calendar table, which is related to the tickets table (calendar - DATE field / tickets - DATEONLY field).

       

      I’m not sure what I might be doing wrong or if there’s an issue with the table, but there are no filters applied to it.

       

      Thank you for any other advise.

       

      Have a good one, 

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Data model looks good. Please provide sample data that fully covers your issue.
        Please show the expected outcome based on the sample data you provided.