topic Re: Calculated column in DAX Commands and Tips

Calculated column

Anonymous — Wed, 18 May 2022 07:23:22 GMT

Hey Everyone. Please l want to find dax that can count the number of cycles of a battery for each component. For each component, a cycle corresponds to a period where the intensity has a cnstant sign and when the intensity changes sign, we go to another cycle. It is therefore necessary to make a calculated column which will identify the cycles of each component, how can l do that?

Thanks in advance for the suggestions.

component	intensity	number of cycle
1	-1	1
1	-2	1
1	3	2
1	4	2
0	4	1
0	5	1
0	-1	2
0	-3	2

En français:

je veux trouver une formule qui permet de compter le nombre de cycle d'une batterie pour chaque composant. Pour chaque composant un cycle correspond à une période où l'intensité a un signe constant et lorsque l'intensité change de signe on passe à un autre cycle.

il faut donc faire une colonne calculée qui va repérer les cycles de chaque composant, comment je peux faire ça? Merci d'avance pour les propositions.

Re: Calculated column

Anonymous — Wed, 18 May 2022 07:57:54 GMT

Hi Alexa,

Do you have a dte column in this table ?

Re: Calculated column

Anonymous — Wed, 18 May 2022 08:15:04 GMT

yes l have a date column.

Re: Calculated column

Anonymous — Wed, 18 May 2022 08:16:56 GMT

And we can have many cycles in one day.

Re: Calculated column

Anonymous — Wed, 18 May 2022 09:34:32 GMT

Bien galère le cas de figure 🙂

Re: Calculated column

tamerj1 — Wed, 18 May 2022 10:27:14 GMT

Hi Anonymous
Here is a sample file with the solution https://we.tl/t-K6pFsGb1qn
It includes 2 calculated columns. The first check for change of sign (checks for new cycle)

New Cycle = VAR CurrentDate = Data[Date] VAR CurrentComponentTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[component] ) ) VAR T1 = FILTER ( CurrentComponentTable, Data[Date] < CurrentDate ) VAR CurrentIntensity = Data[intensity] VAR PreviousDate = MAXX ( T1 , Data[Date] ) VAR PreviousIntensity = MAXX ( FILTER ( T1, Data[Date] = PreviousDate ), Data[intensity] ) RETURN IF ( ISBLANK ( PreviousIntensity ) || PreviousIntensity * CurrentIntensity < 0, 1, 0 )

The 2nd column is the cycle sequential number

cycle # = VAR CurrentDate = Data[Date] VAR CurrentComponentTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[component] ) ) VAR T1 = FILTER ( CurrentComponentTable, Data[Date] <= CurrentDate ) RETURN SUMX ( T1, [New Cycle] )

Re: Calculated column

Anonymous — Wed, 18 May 2022 11:25:40 GMT

😁tamerj1

I will try it. Thank you for your suggestion.

Re: Calculated column

Anonymous — Wed, 18 May 2022 11:59:19 GMT

l don't know why but the second column display the total number of cycles of a day. What l want it's to display the evolution of cycle , for example seeing the 1st cycle then the 2nd then 3rd cycle ,not having the total number directly. is it possible?

Re: Calculated column

tamerj1 — Wed, 18 May 2022 12:08:48 GMT

Anonymous

It wirks in the file that I shared. Can share screenshots?

Re: Calculated column

Anonymous — Wed, 18 May 2022 14:24:26 GMT

j'ai remplacé la colonne date par la colonne date et heure et ça a marché. C'est parceque dans ton tableau pour une date tu as juste compté un cycle pour chaque composant.

Merci pour la formule🤗.

Re: Calculated column

tamerj1 — Wed, 18 May 2022 15:17:11 GMT

Anonymous

Yes you are I forgot to mention that.