cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alexa27
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.

componentintensitynumber of cycle
1-11
1-21
132
142
041
051
0-12
0-32

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
tamerj1
Community Champion
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] )

 

View solution in original post

10 REPLIES 10
tamerj1
Community Champion
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] )

 

Alexa27
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?

tamerj1
Community Champion
Community Champion

@Alexa27 

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

Alexa27
Frequent Visitor

😁@tamerj1 

I will try it. Thank you for your suggestion.

JamesFr06
Solution Supplier
Solution Supplier

Bien galère le cas de figure 🙂

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

tamerj1
Community Champion
Community Champion

@Alexa27 

Yes you are I forgot to mention that. 

Alexa27
Frequent Visitor

And we can have many cycles in one day.

JamesFr06
Solution Supplier
Solution Supplier

Hi Alexa,


Do you have a dte column in this table ?

yes l have a date column. 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

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