topic Re: ALL function disabled when applying date slicer in DAX Commands and Tips

ALL function disabled when applying date slicer

lmiguellf — Wed, 30 Oct 2024 18:55:55 GMT

Hi,

I'm new to power bi, and i'm trying to calculate the OEE in a company.
I'm supposed to calculate the availability for every equipment, then the performance and quality for every component produced and then the OEE.
In that way, i need several slicers to apply the specific filters and calculations i need.

So, basically, i need the availability to make the calculation regarding the equipment, but then ignore the selected component.

For that i use an All function im my measure to ignore component. The calculation is done correctly if i don't have the week interval selected(semana), has you can see below.

If i change the selected component the availability stays the same, as it should, but if i change the week slicer to make the calculation for a specific week or weeks of the year, then everytime i change component in the slicer the availability slicer changes as well.

I don't understand why, but for some reason the week slicer must be disabling the all function.

Here's my measure, you can ignore most of it. It just changes the calculation depending on que equipment selected.

#Disponibilidade =

CALCULATE(

IF(

SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11197" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11198", // Equipamentos que trabalham 24 horas por dia util e 9 horas ao fim de semana

(SUM('dados'[Tempo Seg (con. média)]) + SUM(Paragens[Tempo Paragens(segs)])) / ([#Número Dias Úteis]*24*3600 + [#Numero de dias(fds)]*9*3600 + CALCULATE(SUM('dados'[Horas Extra(s)]),FILTER(dados,dados[Dia da Semana]= "Saturday" || dados[Dia da Semana]="Sunday"))),

IF(

SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11140" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11195" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11196" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11200" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11201" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11203" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11204", // Equipamentos que trabalham 24 horas por dia, dias uteis w 24 horas fds

(SUM('dados'[Tempo Seg (con. média)]) + SUM(Paragens[Tempo Paragens(segs)])) / ([#Numero de Dias]*24*3600),

IF(

SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU13100" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU13051", // Equipamentos que trabalham 24 horas dias uteis

(SUM('dados'[Tempo Seg (con. média)]) + SUM(Paragens[Tempo Paragens(segs)]) ) / ([#Número Dias Úteis]*24*3600),

(SUM('dados'[Tempo Seg (con. média)])+ SUM(Paragens[Tempo Paragens(segs)]) ) / ([#Segundos Uteis] + SUM('dados'[Horas Extra(s)])) // Equipamentos que trabalham 8 horas por dia, dias uteis

))),

ALL(dados[Nome Artigo Prod.])

)

Here's my tables with their relations:

In this measure only the dados and paragens tables are used.

Re: ALL function disabled when applying date slicer

johnt75 — Wed, 30 Oct 2024 15:51:18 GMT

I suspect that the problem is because of the bi-directional relationships. As it is currently, a filter on any table will affect every other table. Unless you have very good reason to use bi-directional relationships you should always use single direction relationships with the one-side filtering the many-side.

Re: ALL function disabled when applying date slicer

lmiguellf — Wed, 30 Oct 2024 18:47:50 GMT

The problem is that i want my filters to be filtering both tables, dados and paragens. Without the bi-directionality i would need to add the columns that i want to use in the slicers in the dim table dados_paragens. However i cannot do this because when i add them, the key column Nº Ordem that i was using to link the tables returns duplicate values and no longer works to link. Also if i create a new key column with the combinations of the slicer columns i don't achieve a unique column as well, so i have no key to link the tables.

Re: ALL function disabled when applying date slicer

johnt75 — Thu, 31 Oct 2024 09:26:32 GMT

You could potentially combine the 2 dimension tables, e.g.

Combined Table = DISTINCT ( UNION ( DISTINCT ( Dados_Paragens ), DISTINCT ( Dados_Teoricos ) ) )

and then link this single table to the fact tables, removing the other relationships. Then the relationships from the new combined table could be single direction.

Re: ALL function disabled when applying date slicer

lmiguellf — Tue, 05 Nov 2024 09:53:41 GMT

Yes, although the problem with the filters persists, since i still can't apply then to the combined table since i get duplicated values.