Forum Discussion

vissvess's avatar
vissvess
Icon for Helper V rankHelper V
6 years ago

Conditional Batch index in DAX

How can I get a calculated column with the following following condition.!

Dataset Sample.


I want a calculated column, for every day, every physical line & every unit status, the new column should start from index 1 arranged by kanbantime in ascending.

Then for every next row, if the current TLC is matching with the earlier, it should return the same index.
If the current TLC & earlier row TLC is not matching, then only the index should increment.

I tried the same with Power query using List.Accumulate. With that, preview loads after some time and refresh fails all the times. So, I am looking for DAX equivalent.

Greg_Deckler , Zubair_Muhammad -Please help with this

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi vissvess ,

     

    Can you share sample data and expected output?

     

    Difficult to follow what is expected output

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Power Query will calculate this without a hiccup within much shorter time than DAX. List.Accumulate is NOT the way to go (it has a completely different usage paradigm), hence your problems. If you create correct M code according to Best Practices for writing M code, it will be blazingly fast (I can guarantee). You'll never get such a speed from DAX. And even though you can do it in DAX, you'll get a much worse compression and hence your formulas could be sluggish when using this column. But do as you wish 🙂
    • vissvess's avatar
      vissvess
      Icon for Helper V rankHelper V
      6 years ago

      Here is what I needed and fullfilled for preview data in power query. But when the same is applied for loading, it simply fails for some 3M row dataset.
      In summary, my data will have Date, Time, Unit Status, Physical Line & TLC against primary key job.
      What I need is, a conditional batch index.
      1. Data to be sorted by Date & then by time within date.

      2. Say, the index should start with 1 for every one day, unit status & Physical Line.

      3. The index should be the same as earlier row, if the current and earlier row has same TLC. Else, increase it by 1.

       

      For the above requirement, I did sorting(1), grouping for (2) and used List.accumulate(3). I am not the best M query expert.

      DAX or Power query solution with better performance is requested.
      Anonymous Anonymous