cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
lulcasl
Frequent Visitor

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] ) )
)
)
 
imagem_2021-03-15_221556.png
 
For example, the "CORRECT 2" column it's the way it should SUM correctly on "MEASURE 1", so the expected result should be this:

imagem_2021-03-15_222005.png

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!
 
1 ACCEPTED SOLUTION
jameszhang0805
Resolver IV
Resolver 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 

jameszhang0805_0-1615872904451.png

Result:

jameszhang0805_1-1615872964601.png

 

 

View solution in original post

2 REPLIES 2
jameszhang0805
Resolver IV
Resolver 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 

jameszhang0805_0-1615872904451.png

Result:

jameszhang0805_1-1615872964601.png

 

 

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?

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors