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 "Comp...
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!

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

        Hello Kelly,

         

        Thank you for taking the time to reply to my issue!

         

        I did as instrcuted, but when creating the measure the DAX returns error after "_previousindex"

         

        pbix.file attached

        PBIX.file 

        It is also the first time for me creating an index table, maybe the issue happened there.

         

        Best regards

        Mat