Forum Discussion
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.
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] )
10 Replies
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] )I will try it. Thank you for your suggestion.
Anonymous
It wirks in the file that I shared. Can share screenshots?
Anonymous
Yes you are I forgot to mention that.