Show a result only once in a table in DAX

[Spotto]

hello everyone, I created several validations (functions) within DAX and two measurements that bring me the accumulated and an indication when the value. The result was a success but the table has the same code repeated when the indicator appears in red (X)

Acumulado sla =

var nrcaso = MAX('Relatório de Histórico de Casos'[Número do caso])

var indice = MAX('Relatório de Histórico de Casos'[INDEX])

var sla = "Y"

var solucao =

CALCULATE(SUM('Relatório de Histórico de Casos'[TOTAL MIN SLA]),

FILTER(ALL('Relatório de Histórico de Casos'),

'Relatório de Histórico de Casos'[INDEX]<=indice),

'Relatório de Histórico de Casos'[Número do caso]=nrcaso,

'Relatório de Histórico de Casos'[Have SLA?]=sla)

return

solucao

------------------------------------------------------------------------

Sla Estourado =

var SlaAcum = _Medidas[Acumulado sla]

var SlaAtual = SUM('Relatório de Histórico de Casos'[SLA Actual])

return

if((SlaAtual - SlaAcum)>0,0,1)

----------------------------------------------------------------------

RESULT:

I would like help to have a solution in DAX so that it only shows the first time that X appeared by code (Nr Case).

Thanks for any help 😉

[tamerj1 · Accepted solution]

Hi @Spotto 

place this measure in the filter pane and select valvue not equal 1

Filter Measue =
VAR CurrentIndex =
    MAX ( 'Relatório de Histórico de Casos'[INDEX] )
VAR PreviousIndex = CurrentIndex - 1
VAR PreviousSlaEstour =
    CALCULATE (
        [Sla Estourado],
        'Relatório de Histórico de Casos'[INDEX] = PreviousIndex
    )
RETURN
    [Sla Estourado] * 2 - PreviousSlaEstour

[Spotto]

thank you very much for your help, but the measure you suggested removes all the items that have the (X) in red. I need you to bring the first time the X (red) appeared by (Nr Case) as shown in photo 2.
I put the power bi code below.

https://drive.google.com/file/d/10acO0PeGIfRahWRSMZzmTuzkrg-HIHhE/view?usp=sharing 

[tamerj1]

Hi @Spotto 
It is working fine with me https://we.tl/t-Glqy4i4SIC

[Spotto]

could you please attach the file with the filter because this option is not shown for me in the filters, I tried all the options and it didn't work, thanks

[tamerj1]

Hi @Spotto 
The WeTransfer Link is there in the comment. Just click to download.

[Spotto]

I understand what happened, the measure worked, thank you very much, but if I add a column as an example, category or a date, the measure stops working. Could you test this possibility to add columns (category, date) and the measure continues to work?

[tamerj1]

Spottoo 
Here is an updated file https://wetll/tRMPAENjjpaa
Yes that is true. When you add a column from adimensionn table the filter context will change by creating a CROSSJOIN between both tables. But this is not a problem in the filter measure. If you want to add any column (for example Category) from a dimension table you can add it as measure

Category = 
CALCULATE ( 
    SELECTEDVALUE ( 'CATEGORIA X PRIORIDADE'[Categoria] ),
    CROSSFILTER ( 'RelatóriodeeHistóricoodee Casos'[Categoria],'CATEGORIA X PRIORIDADE'[Categoria], Both )
)

In the sample file I have, there is no relationship with date therefore adding date has no meaning unless it has a relationship with the fact table. Once the relationship is set then you can follow same procedure of creating proper measure.

[Spotto]

AMAZING, I didn't know it was possible to create columns from measurements, another day of learning. Thank you so much for your time spent helping me.