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!

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!

    • baneworth's avatar
      baneworth
      Helper III
      5 years ago

      Thank you!