Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 =
Solved! Go to Solution.
@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 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:
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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.