Count and Date Problems for Desktop (specific issue)

[baneworth]

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

[baneworth]

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

[selimovd]

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]

Sorry i replied wrong, please see latest reply.

BR

[v-kelly-msft]

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]

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 · Accepted solution]

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]

Thank you!