Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |