New grouping column

[haefnja]

I am looking to add a grouping column that looks at the values in a few different columns to determine the value that should be entered.  My data is from a production table that records the good product and scrap that is created on a shift, as well as the various reasons for the scrap.  The entries on the same shift are grouped by the ReferenceID value, the good product code or scrap code shows in the ProductDescription, and a sorting characteristic in the ProductType that helps show whether it is a good product (G) or scrap (S).  What I want to do is add a column where every entry on that ReferenceID gets the ProductDescription of the good product (ProductType = G) so that I can sort and see which scrap reasons we get when running certain products.  Example of the data is below...can anyone help me out on this?

ReferenceID	ProductDescription	ProductType	What we are running (column I am trying to make)
12323	Q 04	S	5050667
12323	Q 01	S	5050667
12323	QD 30	S	5050667
12323	Q 01	S	5050667
12323	5050667	G	5050667
12430	QD 30	S	5050667
12430	5050667	G	5050667
12515	5081245	G	5081245
12515	QD 31	S	5081245
12515	QD 36	S	5081245
12573	QD 46	S	5081245
12573	5081245	G	5081245
12581	5081245	G	5081245
12581	QD 32	S	5081245
12695	QD 30	S	5081245
12695	5081245	G	5081245
12836	5081245	G	5081245
12836	QD 30	S	5081245
12836	Q 12	S	5081245
12836	Q 09	S	5081245

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(MAX(Data[ProductDescription]),FILTER(Data,Data[ReferenceID]=EARLIER(Data[ReferenceID])&&Data[ProductType]="G"))

Hope this helps.

[haefnja]

So update to close this out, I ended up having to make a few new tables.  first was a new calculated filtered table that just brings in the referenceID and product description for only product type of "G".  Then, I had to make another table that brings in the data from the main table, but import instead of direct query.  once it imports, I can use the lookup value for a new column that looks for the referenceID and returns the good product code.  probably a very round-about way to do it, but it's working so I am happy.  Thanks to @selimovd and @Ashish_Mathur for trying to help!

[haefnja]

Thanks for the help @Ashish_Mathur and @selimovd.  It looks like it should work, but I ran into an issue I was not expecting.  I am using this in DirectQuery mode, since it is a live system that is constantly getting new data entered from the production floor.  Since it is DirectQuery, I cannot use the CALCULATE function...any suggestions?

[selimovd · Accepted solution]

Hey @haefnja ,

DirectQuery is a big difference.

The problem is not CALCULATE, the CALCULATE function works with DirectQuery. The problems are usually the limits for calculated tables and calculated columns.

In your case add the column in your data source, from my point of view that's the best place to do that for your case.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[haefnja]

I don't have direct access to the database that is acting as the data source, so if I can't get it into my data source, is there another way I can go about this? Is there a different way to basically say in a new column "For all the same ReferenceID values, take the ProductDescription when ProductType = G"? I think you understand well enough what I am trying to do, I just don't understand why I can't get it to work properly.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(MAX(Data[ProductDescription]),FILTER(Data,Data[ReferenceID]=EARLIER(Data[ReferenceID])&&Data[ProductType]="G"))

Hope this helps.

[selimovd]

Hey @haefnja ,

the following calculated column should give you the desired result:

Description G =
VAR vRowReferenceID = myTable[ReferenceID]
VAR vDescr =
    CALCULATE(
        MAX( myTable[ProductDescription] ),
        myTable[ProductType] = "G",
        myTable[ReferenceID] = vRowReferenceID,
        myTable
    )
RETURN
    vDescr

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic