actually i have a table which has two columns, one is time and other is status…
time is in increasing order always…
Status column has values 1 or 0 or missing values…
I need a third column known as result… which should sum up all the ones till a row of 0 value has come .
Example in status column row wise data is like 0,1,1,1,0,0,0,0,1,1,1,1,10,0,0
5 After any row having value 1,when ever a 0 will appear in any row of status column…we will say round completed…so we have to sum all the ones for that round…
suppose … 0,1,1,1,0,0,0,0,1,1,1,1,1,0,0,0 —here first round is completed at 5th digit which is zero and appear after 1…so will sum all the ones before it and after any previous zero… so in this case it will be 3…
Now 9th digit which is 1 marks beginning of new round and same round ends at 14th digit which is 0…so total of ones which any previous zero is 5.
may be i am not able to explain properly… so i am attaching…a excel file…in which i have also shown the desired output.
any help will be appreciated.
regards draft.xlsx (12.0 KB)
*** READ FIRST ***
Before you send your question.
Make sure that all details relevant to your question is complete:
Your current work-in-progress PBIX file - VERY IMPORTANT
A clear explanation of the problem you are experiencing
Dear sir thanks for responding. and sparing your precious time.
null values have to be there as it is . we cant remove those… internally there is no restriction in assuming anything… just that requirements needs to be achieved.
Added Column Previous Row
We add a calculated column with the STATUS value of the previous record.
For this I have used a function called Table_PreviousRow created by Imke Feldmann, instead of using the creation of two indexes.
Added Column Round Completed
We add the Rounded Completed column which is 1 if the current STATUS value is 0 and the previous STATUS value is 1.
Added Index from 1
We add an index starting with 1, which we will use to determine the range of rows between a Full Round and the Previous Round.
Add Index Previous Round Completed
We add a column with the index of the last Complete Round for those records with Complete Round = 1.
We remove null values from the previous column.
Added Column Result
We add a column with the sum of the STATUS column for those rows whose index is between the index of the last Complete Round and the current index.
Thanks a million sir…
thanks to your logic i will try to implement same logic using dax…unfortunately there is no while loop and break statement in dax else implementation would have been easier.
thanks again sir for your precious time and zeal to help others…if you have idea about implementation of while loop sort of thing using dax…please guide … regards
Hi @praveer82,
Since it is a static calculation I recommend doing it from Power Query instead of DAX, but if you have another reason to do it in DAX I can look at it.
Thanks a lot sir for reverting back…
Actually sir when data is coming from direct query mode…in that case we wont be able to apply some power query transformations…that why was thinking of using dax…
regards
Hi @praveer82,
Can you create an index for this table in the database to sort it based on the TIME field?
If you didn’t have duplicate values in the TIME field, we could determine which record is the previous one from DAX without using the index.
Thanks again for reverting…
Actually sir i am not permitted to make any changes or create any new object like views in database. Only option is to use the data the way it is… That’s why was thinking of dax…
Hi @praveer82,
To do it from DAX we need to have an index with which we can determine the internal order of the records and be able to determine which is the previous row. Since we only have the TIME column with repeated values, I can’t find a way to create an index based on this column.
