cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated column

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.

1 ACCEPTED SOLUTION
Community Champion

Hi @Alexa27
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] )``````

10 REPLIES 10
Community Champion

Hi @Alexa27
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] )``````

Frequent Visitor

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?

Community Champion

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

Frequent Visitor

I will try it. Thank you for your suggestion.

Solution Supplier

Bien galère le cas de figure 🙂

Frequent Visitor

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🤗.

Community Champion

Yes you are I forgot to mention that.

Frequent Visitor

And we can have many cycles in one day.

Solution Supplier

Hi Alexa,

Do you have a dte column in this table ?

Frequent Visitor

yes l have a date column.

Announcements