Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] )
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] )
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?
@Anonymous
It wirks in the file that I shared. Can share screenshots?
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🤗.
@Anonymous
Yes you are I forgot to mention that.
And we can have many cycles in one day.
Hi Alexa,
Do you have a dte column in this table ?
yes l have a date column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |