Forum Discussion

lulcasl's avatar
lulcasl
Frequent Visitor
5 years ago
Solved

Measure resulting on wrong value within the context

There are two rolling measures I'm trying to achieve but both of them are NOT working as I think they should.

There are no blank/empty/null values (pbix attached in the end) and yet it still doesn't work.

 

I'm trying to calculate a "rolling distinc count" based on shifts and my subtotals are resulting on different values than they should.

My Measure 1 is basically this:

On shift 1 (Turno 1, between 0h to 7h ), I'm calculating the DISTINCTCOUNT of each f_MovMad[Placa do Cavalo] line within the shift.
On shift 2 (Turno 2, between 8h to 15h ), I'm calculating the DISTINCTCOUNT of each f_MovMad[Placa do Cavalo] line within shift 2 AND shift 1 (between 0 am to 15 pm)
On shift 3 (Turno 3, 16h to 23h), I'm calculating the DISTINCTCOUNT of the whole day (Turno 1, 2 and 3).

Heres the DAX:


Measure 1 =

SWITCH(
MAX(f_MovMad[Turno]),
"Turno 1", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), f_MovMad[Turno]="Turno 1" ),
"Turno 2", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), f_MovMad[Turno]<>"Turno 3"),
"Turno 3", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), ALL(f_MovMad[Turno])))
 
The exact same error is happening with me with the SUM running total on Measure 2:

Measure 2 =
CALCULATE (
COUNT(f_MovMad[Placa do Cavalo]),
FILTER (
ALL ( f_MovMad[Turno] ),
f_MovMad[Turno] <= MAX ( ( f_MovMad[Turno] ) )
)
)
 
 
For example, the "CORRECT 2" column it's the way it should SUM correctly on "MEASURE 1", so the expected result should be this:



The values within "Turno 3" should always be the same as the subtotal, since they both calculate within the same context, whole day.
I'm stuck with this formula for a couple of days now, I would be extremely grateful if someone could help me troubleshoot this!
 
  • lulcasl  Please correct your measure to :

    Contagem Placas AC = 
    SWITCH(
        MAX(f_MovMad[Turno]),
        "Turno 1", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), f_MovMad[Turno]="Turno 1" ),
        "Turno 2", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), f_MovMad[Turno]<>"Turno 3"),
        "Turno 3", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), ALLSELECTED( f_MovMad[Turno]))
    )
    
    Viagens Realizadas ACC = 
    VAR _Max = MAX( 'f_MovMad'[Turno] ) 
    RETURN 
    CALCULATE( 
        [Viagens Realizadas],
            REMOVEFILTERS(),
            f_MovMad[Turno] <= _Max ,
            VALUES( 'd_CadTransportadoras'[Tipo de Transporte] ),
            VALUES( 'd_Calendário'[Data] )
        )

    And the selected column in your chart 

    Result:

     

     

2 Replies

  • lulcasl  Please correct your measure to :

    Contagem Placas AC = 
    SWITCH(
        MAX(f_MovMad[Turno]),
        "Turno 1", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), f_MovMad[Turno]="Turno 1" ),
        "Turno 2", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), f_MovMad[Turno]<>"Turno 3"),
        "Turno 3", CALCULATE( DISTINCTCOUNT(f_MovMad[Placa do Cavalo]), ALLSELECTED( f_MovMad[Turno]))
    )
    
    Viagens Realizadas ACC = 
    VAR _Max = MAX( 'f_MovMad'[Turno] ) 
    RETURN 
    CALCULATE( 
        [Viagens Realizadas],
            REMOVEFILTERS(),
            f_MovMad[Turno] <= _Max ,
            VALUES( 'd_CadTransportadoras'[Tipo de Transporte] ),
            VALUES( 'd_Calendário'[Data] )
        )

    And the selected column in your chart 

    Result:

     

     

    • lulcasl's avatar
      lulcasl
      Frequent Visitor

      You're a lifesaver! Worked perfectly.
      As I could see I was using the wrong column for the context ("d_CadTransportadoras"[Tipo de Transporte]) but I didn't urderstand the difference between ALL and ALLSELECTED for the "Contagem Placas AC" measure, can you explain the logic difference between them?