Creating a measure ( like not exists for sql )

Question

I would like to ask for your help with a question regarding Analysis Services (tabular).

Here at the company, no one has knowledge, and I'm having to manage on my own.

I need to create a measure that counts service request rows. These service requests are in a dimension called DimPrimes, where updates are made daily using SCD type 2 strategy, but only for the Status columns (keeping the history). The rest is updated normally, and every day a job runs that populates the FatDatasPrime table.

I need to create a measure that calculates the quantity of service requests with a status of 1 within the last week. However, if any service request has a status of closed (status = 2) within the same week, we should not count that request as open.

In SQL, I can perform this query and get the result I need. However, to create a measure that can use "not exists," I can't find any information. Would it be possible to create a measure with the logic provided below?

select
count(1)
from
FatoPrimeDatas fat inner join
dimprime pri on
pri.id = fat.IdNrAtendimento_SK inner join
DimTempo tmpABE on
tmpABE.id_tempo = fat.IdTempo_DtAtendimento_SK
where
tmpABE.data >= @FILTRO_DataSelecionada_Calculada and tmpABE.data <= @FILTRO_DataSelecionada and /*ultima semana*/
 pri.CdStatusAtendimento in (1,2,3,4) AND
 pri.CdTipoAtendimento not in (3)
and not exists(
select
1
from
FatoPrimeDatas fat2 inner join
dimprime pri2 on
pri2.id = fat2.IdNrAtendimento_SK inner join
DimTempo tmpEnc on
tmpEnc.id_tempo = fat2.IdTempo_DtEncerramento_SK
where
pri2.NrAtendimento = pri.NrAtendimento and
tmpEnc.data >= @FILTRO_DataSelecionada_Calculada and tmpEnc.data <= @FILTRO_DataSelecionada and /*ultima semana*/
pri2.CdStatusAtendimento >= 5 and
pri2.CdTipoAtendimento not in (3)
)

Answer 1

In SQL, I can perform this query and get the result I need. However, to create a measure that can use "not exists," I can't find any information

Then model it on relational database side in SQL; in MDX/DAX it's more then difficult.

Answer 2

DAX equivalent for not exists is https://learn.microsoft.com/en-us/dax/except-function-dax

With what's known of your current data model, you could go along the lines of

var status1 = calculatetable ( selectcolumns ( 'FatoPrimeDatas', related ( 'dimprime'[NrAtendimento] ) ), <filters for last week status 1> )

var status2 = calculatetable ( selectcolumns ( 'FatoPrimeDatas', related ( 'dimprime'[NrAtendimento] ) ), <filters for last week status 2> )

var rows2count = except ( status1, status2 )

return countrows ( rows2count )