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