Forum Discussion
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 =
Measure 2 =
)
https://www.dropbox.com/s/ojl7vtzafw3l4ui/data%20sample.pbix?dl=0
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
- jameszhang0805Resolver IV
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:
- lulcaslFrequent 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?