Calculated table on fact table

[gmq]

Good day,

I'm trying to filter a fact sales item table.

I need to extract all the customers' ID based on a product category and order number; in order to analyse their customer behaviour (i.e. what they purchased on their 2,3,4,5 etc. orders). I believe I need to use a calculated table and filter the sales item fact table, but honestly it is a little out of my depth, can anyone give me a few pointers so that I can educate myself?

The model contains several tables but I think we can focus on two for this to work.

Dim_products: Contains PLU (basically SKU) and Product Category. I need to filter for product category = 'pizza'

Sales_Item: Contains Customer ID, Transaction_Counter (as in first, second third customer order), PLU.

In my mind what I want is:

1. if Transaction_Counter=1 and PLU(related)=soups; then give me the customer ID

2. filter sales item using said subset of customers 

3. use the existing visuals to measure the sales mix across the following orders

Can anyone point me in the right direction?

Thanks!

Not sure if it is relevant, the model is import mode, but I'm working on desktop, so I can't directly change the table structure and need to manage via DAX.

A Dim_Customer table exists, but I don't think it is necessary for this exercise, do let me know if I'm mistaken

[gmq]

Hi @Anonymous ,

Thanks so much for your assistance, I can't believe the answer could be so simple.

However, when testing it in our Production environment, I realised I only have a direct query mode, as such I don't have access to the data pane. Is there a way to create the calculated table in the report view?

Alternatively, I should be able to create it Tabular Editor, just not sure how yet...

So far I've created two measures

L_NC_Soup_Table = FILTER(LoyaltyMemberTransactionItem,

LoyaltyMemberTransactionItem[LoylatyTransactionCounter]=1 && // can edit this to see other order counter

RELATED('Product'[Product Category])="Soup")


And

L_NC_Soup = CALCULATE(

    COUNTROWS(LoyaltyMemberTransactionItem),

    FILTER(LoyaltyMemberTransactionItem, LoyaltyMemberTransactionItem[LoyaltyMemberKey] in VALUES('L_NC_Soup_Table'[LoyaltyMemberKey])

))
But I get an error saying measure L_NC_Soup failed because L_NC_Soup_Table cannot be found.

[Anonymous · Accepted solution]

Hi @gmq ,

Change the relationship between the two tables to inactive, create a measure:

Measure = 
VAR _Category=MAX('Dim_products'[Product Category])
VAR _PLU=CALCULATE(MAX('Dim_products'[PLU]),FILTER(ALL('Dim_products'),'Dim_products'[Product Category]=_Category))
VAR _CustomerID= CALCULATE(MAX('Sales_Item'[Customer ID]),FILTER(ALL('Sales_Item'),'Sales_Item'[PLU]= _PLU && 'Sales_Item'[Transaction_Counter] = 2 ))
RETURN
CALCULATE(COUNTROWS('Sales_Item'),FILTER('Sales_Item','Sales_Item'[Customer ID]=_CustomerID))

Result:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Anonymous]

Hi @gmq ,

Based on your description I created simple data:

Create a calculated table:

table = FILTER('Sales_Item','Sales_Item'[Transaction_Counter]=2&&RELATED('Dim_products'[Product Category])="pizza")

Create a measure:

Measure = CALCULATE(COUNTROWS('Sales_Item'),FILTER('Sales_Item','Sales_Item'[Customer ID] in VALUES('table'[Customer ID])))

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.