Group By Challenge

[danish169]

Afternoon,

I am struggling with an expression and I think it might be related to a group by as Im familiar with this in sql just cant crack it in dax. Here is the data set:

There are 3 product_id in this example. Product checks (check_id) 1 to 5 are not sequential. When a check is done an entry is made into this table. 

Check 1 is required to be done for the product_id to appear in this list. 

Checks 2,3 and 4 can occur in any order. Check 5 can only be done if checks 1,2,3 and 4 have all been done. 

In the data above:

product ID 15104 has had check 5 completed. 

Product Id 16349 has had check 2 complete 
Product id 16271 has had 2,3,4 checks complete and is awaiting check 5. 

What I need to do is calculate the number of products that have had check 5 done vs how many products are awaiting checks in the system.  Products awaiting checks in the system are those that have had check 1 done. They may have 2,3, or 4 done but not neccessarily all three. 

In this case total products in the list is three and one product has had check 5 complete. The answer should be 33% if we disregard created_at_day. 

As I am applying a date filter to see how many check 5's have been done in a given window of time I need total products to ignore the date slicer I use on created_at_day. 

Deleted_at can be ignored as i will remove it from the data. There are checks 6 and 7 so 5 is not the maximum value. 

I then need to work out how many products have check_id 1 and 2 done only. 

Tried many combinations of group by and agregation to no success. Hopefully someone can help me out! 

[Whitewater100 · Accepted solution]

Hello:

Can you look at this for possible answer?

https://drive.google.com/file/d/1i5EXvqx_1FXgCqVRd9dDss-e4nmdWeV6/view?usp=sharing 

[Whitewater100 · Accepted solution]

Hello:

Can you look at this for possible answer?

https://drive.google.com/file/d/1i5EXvqx_1FXgCqVRd9dDss-e4nmdWeV6/view?usp=sharing 

[danish169]

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

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.