Forum Discussion
Group By Challenge
Hello:
Can you look at this for possible answer?
https://drive.google.com/file/d/1i5EXvqx_1FXgCqVRd9dDss-e4nmdWeV6/view?usp=sharing
Hi:
I hope you can mark first reply as solution.
I just replied and lost it all because I was too slow. Here it is for the second time:-)
I will reply to your message text here.
I got a chance to look at this file so thanks for taking the time. It gets us a little way forward and interesting to see how you did the calculated column. I do have some questions though if you could be kind enough to answer them.
1. What does Checks[Prod ID] and Checks [CheckID] do in the code in the calculated column, struggling to follow it mainly here:Checks[Check ID] IN {1, 2} &&
Checks[Check ID] = vcheck)
&& Checks[Prod ID] = vprod)There is a few ways to bring back the original row context. Because a calc column is going row by row, when we introduce Calculate it takes each individual row and turns that one row into a new filter. Because we want to work in the original row context we use this type of calculated column. Here is another way, it uses function "EARLIER" meaning bring back to original context.
Check1 alt =
CALCULATE(COUNT(Checks[Prod ID]),
Checks[Check ID] IN {1, 2} &&
Checks[Check ID] = EARLIER(Checks[Check ID] &&
Checks[Prod ID] = EARLIER(Checks[Prod ID])))
2. We need to calculate products awaiting a check 4. The combination would be check 1 done and check 4 outstanding. How do we do that in the calculated column? I have tried combinations of "not IN" to no avail. I agree it can be confusing.
Check1 not 4 =IF(CALCULATE(COUNT(Checks[Prod ID]),Checks[Check ID] IN {1,4} ,ALLEXCEPT(Checks,Checks[Prod ID],Checks[Check ID]))=1,1,BLANK())3. The last bit is regarding the number of checks done vs total products. The count of check 5 done must be responsive to the data slicer on the page whilst the total products (the distinct count) needs to be for everything in the system regardless of date it what created.
I would have a separate table of just products. You can bring these two measures in and they will dynamically update your table.
Count of 5 = CALCULATE(DISTINCTCOUNT(Checks[Check ID]),Checks[Check ID] =5)Count of Products = CALCULATE(DISTINCTCOUNT(Checks[Prod ID]),ALLEXCEPT(Checks,Checks[Prod ID]))Calc Col in Products Total = [Count of Products]Calc Col for 5's = [Count of Five]I hope this helps.
Hi:
I hope you can mark first reply as solution.
I just replied and lost it all because I was too slow. Here it is for the second time:-)
I will reply to your message text here.
I got a chance to look at this file so thanks for taking the time. It gets us a little way forward and interesting to see how you did the calculated column. I do have some questions though if you could be kind enough to answer them.
1. What does Checks[Prod ID] and Checks [CheckID] do in the code in the calculated column, struggling to follow it mainly here:
Checks[Check ID] IN {1, 2} &&
Checks[Check ID] = vcheck)
&& Checks[Prod ID] = vprod)
There is a few ways to bring back the original row context. Because a calc column is going row by row, when we introduce Calculate it takes each individual row and turns that one row into a new filter. Because we want to work in the original row context we use this type of calculated column. Here is another way, it uses function "EARLIER" meaning bring back to original context.
Check1 alt =
CALCULATE(COUNT(Checks[Prod ID]),
Checks[Check ID] IN {1, 2} &&
Checks[Check ID] = EARLIER(Checks[Check ID] &&
Checks[Prod ID] = EARLIER(Checks[Prod ID])))
2. We need to calculate products awaiting a check 4. The combination would be check 1 done and check 4 outstanding. How do we do that in the calculated column? I have tried combinations of "not IN" to no avail. I agree it can be confusing.
3. The last bit is regarding the number of checks done vs total products. The count of check 5 done must be responsive to the data slicer on the page whilst the total products (the distinct count) needs to be for everything in the system regardless of date it what created.
I would have a separate table of just products. You can bring these two measures in and they will dynamically update your table.
Sorry major delay getting back to you. Original solution accepted :D.
Yea the context and filters keep cropping up and they can get confusing. Your explantion makes sense and EARLIER as a function made it stick.
Im trying to implment this into my live data now will let you know how I get on.