calculate expression which use another measure calculation to filter those long than X days

AdrianLock — Mon, 26 Jun 2023 14:43:33 GMT

Hi everyone I have an issue which I'm sure is simple but I cannot get it to work. (as our dataset is huge and not easy for me to scrub some data out off ill try and explain.

Currently we have a table iv_fact_ticketsall. This is the fact table for out ITSM tool. It has large number of dimensions and some other fact tables associated with it. One of them contains every status the ticket has been assigned at some point and the length it was on. This is called IV_Fact_TicketChange. Now I have a measure _StatusLength(Minutes) in IV_Fact_TicketChange which works out the length of each status based on BeginDateTime & EndDateTime fields. With some dax to takeout weekends and bankholidays. Inside that table there is also a field TicketStatusSK. Which holds the status. Both tables are linked by the reference number (TicketNumber) so each row in iv_fact_ticketsall table would have multiple entries in the iv_fact_ticketchange.

I need to get total length of a ticket. Based on combination of certain status's and which is less than 5 days or in my case 7200 minutes.

Now I can do this easily as create calculated column:-

CALCULATE(SUM(IV_fact_TicketChange[_StatusLength(Minutes)]), IV_fact_TicketChange[TicketStatusSK] in {"Resolved","Closed","Fulfilled"})

Then use this with in my measure to aggregate down all the rows which are <= 5 days. then divide this by the total number of tickets for a given month. Such as: -

_KPI1 =

VAR _TicketsinSLA =

CALCULATE (

[_TicketsCount], //This is measure to count number of rows in IV_Fact_TicketsAll Table)

IV_dim_ReportedCategory[ReportedCategoryNameSK] IN {"Amend Existing User Account","Delete or Disable User Account","New User Account","New User Account with Hardware"},

IV_fact_TicketsAll[TotalTime(Minutes)] <=7200

)

VAR _TotalTickets =

CALCULATE (

[_TicketsCount],

IV_dim_ReportedCategory[ReportedCategoryNameSK] IN {"Amend Existing User Account","Delete or Disable User Account","New User Account","New User Account with Hardware"}

)

RETURN

DIVIDE (

_TicketsinSLA,

_TotalTickets,

)

However in this instance I need to create it in the\a measure used in a calculation for KPI. (Unfortunately for this one particular KPI they only want to count certain status and as its not need other areas I rather it was not a calculated column.) I'm sure this is pretty Simple but cannot get it to work

topic calculate expression which use another measure calculation to filter those long than X days in DAX Commands and Tips

calculate expression which use another measure calculation to filter those long than X days