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...
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.

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.