Forum Discussion

Uka's avatar
Uka
Frequent Visitor
3 years ago
Solved

Rolling Count that resets when a row value is zero

Hello ,

Please could you help me to calculate the rolling count of a column (~Category1 No) in AGG_Worked_Hours table; such that when the value is 1, it will do a rolling count but once the value become 0, it will reset count to 0 and start counting the rolling again. This process would continue as a loop untill the end of the data.

 

This should be able to return the rolling count of each row as below when an Employee_ID is selected from Dim_Employee table.

 

Please can someone give me a hint on how to attched the pbi file and sample dataset?
Many thanks for your help! 

11 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi, 

    Please check the below attached pbix file.

    It is quite difficult to understand the sample that you provided, but I tried to create my own sample.

    I am not sure if I understood your requirement correctly, but I tried to reset calculation every zero by using the concept of PARTITIONBY DAX function.

    1. create a partition column in the table.

    2. use this partition column in WINDOW DAX function.

     

    • Uka's avatar
      Uka
      Frequent Visitor
      3 years ago

      Hi Jihwan_Kim 

      Many thanks for your help!  I really appreciate; but I got an error below; 

      Please can I send my pbi file for you to see?

      If yes, please can you talk me through how to attach the file as I can't find the attach botton in this window.

       

      Many thanks

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi, 

        I am not sure whether you can attached the file or not, but you can also share the link of the file.

        If it is OK with you, please share the link (Onedrive link or googledrive link or dropbox link), and then I can try to look into it.

        Thank you.