Classify if the ticket have passed through the error range (Start of the issue, End of the issue)

[alfonsoasenjo]

Hello everyone

I'm wondering if somebody can give me light to my issue how to do or if its even possible to query. Basically I have two tables, "rank failure" where are all the periods of times that the principal system had problems in the connection (Start of the issue, End of the issue) 

and the principal table "Principal" where i have all the tickets that have been made, with there respective (Created date and Resolution Date). 

The thing i would like to do is add a column on principal, column that classify if the ticket have passed through the error range. ex. "OK", "FAILURE", or made a new table with all the tickets that have passed through the error range.

I try using If formula

State = IF(principal[created]<rank failure[Inicio]&&rank failure[Inicio]<principal[resolution]||principal[created]>rank failure[Inicio] && principal[created]<rank failure[Fin],"Failure","OK")

But it mark it as syntax error, I have already match the column created with the column inicio* Im not really sure if Im doing ok

Any help is really appreciated. Thanks a lot

Maria J.

[amitchandak]

@alfonsoasenjo , visual in not clear, but seem like one part of if is date and another one 0. they are two different data type.

Make 0 as blank()

[alfonsoasenjo]

yes i used this: 

Fecha de Resolución = IF(ISBLANK(principal[resolutiondate].[Date]),0,principal[resolutiondate]),

and then replace: 

if(countx( filter('rank failure', principal[created]< 'rank failure'[Inicio]
&& 'rank failure'[Inicio]<principal[Fecha de Resolución]
||principal[created]>'rank failure'[Inicio]
&& principal[created]<rank failure[Fin]),'rank failure'[Inicio] )+0 >0,"Failure","OK")

Thank you very much for all your help,

Its solved 🙂

[amitchandak]

@alfonsoasenjo ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[alfonsoasenjo]

I could solve it somehow, by replacing the column of "principal[resolution]", there where blank data of the tickets that have not been resolved yet. i replace the blank data with 0. I dont know if you have a better solution for it. Thanks you for the help

just replace the principal[resolution] with principal[Fecha de Resolución]

[amitchandak]

@alfonsoasenjo , Are Inicio and fin have datatype date or text. Make sure all dates have datatype date

[alfonsoasenjo]

yes, all the columns have datetype format

[amitchandak · Accepted solution]

@alfonsoasenjo , Create a new column in principal like

if(countx( filter('rank failure', principal[created]< 'rank failure'[Inicio]
&& 'rank failure'[Inicio]<principal[resolution]
||principal[created]>'rank failure'[Inicio]
&& principal[created]<rank failure[Fin]),'rank failure'[Inicio] )+0 >0,"Failure","OK")

[alfonsoasenjo]

Hello! thank you for the prompt reply,

I used the formula that you suggest, but I still have the syntax error. "DAX comparison operations do not support comparison values of type Date with values ​​of type Text. Consider using the VALUE or FORMAT function to convert one of the values". Just to clarify all dates in both tables are in DATE/HOUR format.

Thanks you for the help, I feel Ill get it soon