Forum Discussion
Helper IGroup By Challenge
Solution SageHello:
Can you look at this for possible answer?
https://drive.google.com/file/d/1i5EXvqx_1FXgCqVRd9dDss-e4nmdWeV6/view?usp=sharing
Helper IHi there,
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)
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.
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.
- Whitewater100
On the Calc Column Explanation:
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 each original row into a new filter. Because we want to work in the original row context we use this type of calculated column to jump back to our original row by row. Here is another way, it uses function "EARLIER" meaning bring back to original context. I used variables but Earlier does the same thing. One way I think about this is that if I want to group an answer, say by product number, I need to do this process to get answers witihin the same product numbers, in a calculated column. The ALLEXCEPT function does something similiar. This entire subject has a lot of materials explaining better then I. Basically anything on calculated columns, row context and how the CALCULATE function not only can modify filter context but also can turn calculated column rows into new filters.
Hope I don't confuse!
- Whitewater100
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.- danish169
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.