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 The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform. #### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison! #### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022. Top Solution Authors
Top Kudoed Authors
Users online (1,530)