Creating groups based on numbers

[rhborders]

I would like to be able to create a column that calculates the average date that an invoice is created. I need this average to be filtered based on both product ID as well as customer ID. I was able to create a working calculated table in Visual Studio that looks like this: =GROUPBY(Fact_Invoice_Line,Fact_Invoice_Line[Customer_ID],Fact_Invoice_Line[Product_ID],"Invoice Means",(Fact_Invoice_LineAVERAGEX(CURRENTGROUP(),Fact_Invoice_Line[Invoice_Date_ID]))) . The issue that I ran into with this is that I need to create a key to connect this calculated table that does the same thing, but instead of finding the average it finds the standard devation. I tried to concatinate the product ID and customer ID but I have repeating values of -1 that are not needed and create duplicates. What I am trying to work out is a way to remove the -1 from Product ID and Customer ID or filter them out so that I can create a relationship to be able to put both of them in a Power BI data set. If someone knows how to directly do this work in Power BI that would work as well. The reason I created it in Visual Studio is because Power BI wouldn't accept my GROUPBY function.

[Thejeswar]

Hi @rhborders,

Between your Fact Table in Power BI and the New Calculated table, introduce a new bridge table that will have only one column. This column will be the concatenation of CustomerID and ProductID.

Now join your fact table and Calculated table through this bridge table

Note: Your fact table and Calculated table also will have the concatenated column. Only then you can connect the tables

Something similar to the image given below

[rhborders]

Hey @Thejeswar,

I tried to do this and ran into the problem of having repeat values so it couldn't be a unique key. In both my customer ID and Product ID I have -1 representing when our company makes a sale versus one of our sales reps making a sale. This results in the multiple values that repeat. I had tried to write out a function that filtered out the -1 but couldn't find one that worked.

[Thejeswar · Accepted solution]

@rhborders,

Did you remove the duplicates in your bridge table??

If not, then remove it. 

Also it doesn't matter how many every repeating values you have in your table. Since you are going to remove the duplicates from your bridge table, your bridge table will act as a unique key for joining the tables