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.

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.