Forum Discussion

gmq's avatar
gmq
Frequent Visitor
1 year ago
Solved

Calculated table on fact table

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


Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • gmq's avatar
    gmq
    Frequent Visitor
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.