Forum Discussion

baneworth's avatar
baneworth
Helper III
5 years ago
Solved

Count and Date Problems for Desktop (specific issue)

Hello All,

 

I don't have to use PowerBI that often so see me as an beginner.

I will try my best to describe this issue as clear as i can.

 

Products go from "Build" to "Test" to "Prep" to "Completed" Status.

This steps are signaled by CompleteFlag1, CompleteFlag2 & CompleteFlag3 and those only show 0 & 1 (1 obviously for flag cpompleted)

- as example: Build lasts from Start until CompleteFLag1 switched to 1 & Test lasts from CompleteFlag1 = 1 until CompleteFlag2 = 1, etcetc

CompleteTime1, CompleteTime2 & CompleteTime3 show when the state has been changed (in Date form)

 

 

What i have to do now:  Write a DAX that counts Products in Test Status

Products where CompleteFLag1 = 1 & CompleteFLag2 = 0

(as soon as flag2 switches to 1 i dont need to count it anymore since it will leave testbay)

 

Another Issue: Show me counted Data daily for products

In the table you can see that CompleteTime* shows 1 Date entry (marked by operator in pogramm). 

I need to be able to see Products (counted) which are in the state of Completeflag1 = 1 & completeFlag2 = 0  on daily basis.

as example: Product A on 3/3 how many in Test, 3/4 how many in Test, Product B ......

 

If someone could help me out with the Dax for count and the Date issue (continoues counting daily basis for Test Status) that would help me a lot.

 

I also added the pbix. which will be more helpful than all the explaining i guess.

 

https://drive.google.com/file/d/1bayFTVYcDnf7B6zIeHVAfC07dfCqBj_t/view?usp=sharing

 

Best regards

Mat

 

Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi baneworth ,

     

    Because you are missing right parentheses after "Filter" function.

    Try below measure :

    measure = 
var _index=CALCULATE(MAX('pdlTest'[Index]),FILTER(ALL('pdlTest'),'pdlTest'[CompleteFlag1]=1&&'pdlTest'[CompleteFlag2]=1))
Return
CALCULATE(COUNTROWS('pdlTest'),FILTER(ALL(pdlTest),'pdlTest'[CompleteFlag1]=1&&'pdlTest'[CompleteFlag2]=0&&'pdlTest'[Index]<_index))

    Then you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

7 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hello Mat baneworth ,

     

    you would get the desired result with the following measure:

    Products in Test = 
COUNTROWS(
    CALCULATETABLE(
        pdlTest,
        pdlTest[CompleteFLag1] = 1,
        pdlTest[CompleteFLag2] = 0
    )
)

     

    In order to analyze by date you can just use the measure in a visual with the desired dates.

    You should consider using a dimensional date table as it will make your life easier. Check that tutorial for this:

    Power Bi for Beginners: How to create a Date Table in Power Bi

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark it as solved and give it a thumbs up 👍?!

    Best regards
    Denis

    • baneworth's avatar
      baneworth
      Helper III
      5 years ago

      Sorry i replied wrong, please see latest reply.

      BR

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi baneworth ,

         

        First create an index column in table pdTest;

        Then create a measure as below:

        measure=
var _index=CALCULATE(MAX('pdTest'[Index]),FILTER(ALL('pdTest'),'pdTest'[CompleteFlag2]=1&&'pdTest'[CompleteFlag1]=1)
var _previousindex=MAX('pdTest'[Index])-1
var _previousflag2=CALCULATE(MAX('pdTest'[CompleteFlag2]),FILTER(ALL('pdTest'),'pdTest'[Index]=_previousindex)

Return
If(_previousflag2=0&&MAX('pdTest'[CompleteFlag2])=1,CALCULATE(COUNTROWS('pdTest'),FILTER(ALL('pdTest'),'pdTest'[Index]>_index&&'pdTest'[Index]<=_previousindex

        If you still cant work out,pls add an index column then share the .pbix file again.(Remember to remove the confidential information)

         

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!

  • baneworth's avatar
    baneworth
    Helper III
    5 years ago

    Dear Denis,

     

    Thank you for quick reply and i followed your input.

    It is a hard for me to tell you that your recommended solution for count works since i got a problem i guess.

     

    First, i added the dimension table (kept it simple for testing), but it seems there is nothing appropriate to link it to my dataset.

     

     

    I tried linking it to "CompleteTime1" since this is also where CompleteFlag1 is related to.

    But then it only counts the Flag1 & Flag2 condition the time it actually got entered by Technicians and not the other days following until Flag2 also switched from "0" -> "1".

     

    isn't this the big problem here? not being able to link the data table accoridngly to my set?

    - i tested with table to see daily count of the products, but they get counted one time only (Completetime1 date)

     

    Best regards

    Mat