• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Table relationships and crossfilter

R

redroller

New Member
I am working with a data model where the main fact table shows transaction records (Product, Customer Company, Customer Name, Salesperson, Date, Amount, Transaction ID etc.). There are a couple connected dimension tables (Customer tiering, Sales Teams). I have a few measures to count transactions, classify customers as low/medium/high volume. This to create a pivot table with a slicer by Salesperson to show their customer breakdown and transaction history.

I added another table to the model (Assignment) that contains a record for each sales person vs. each customer and a Yes/No field to designate whether a customer was assigned to that salesperson. I created a relationship to the Sales Teams table, but the direction becomes inbound.

I am quite new to this and tried to use a CROSSFILTER measure. I want to add a second slicer to my pivot table so that I can filter the Yes or No customers against a salesperson. But instead of just filtering the view, clicking Yes or No changes the values of my customer classification measure.

Any ideas what I might be doing wrong? Apologies that I cannot share the work file and can't recreate with my Mac at home.
 
Chihiro said:
My recommendation is to avoid snowflake (dimension related to dimension) whenever possible and use Star schema or Star Constellation schema (collection of star schema). It will make your downstream DAX calculations that much easier to maintain. Have a read of...
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
The importance of star schemas in Power BI - SQLBI

It is rare that you will need or want snowflake schema.
Click to expand...
Thanks for the feedback... I've read through the two articles. But in my scenario, the "Assignment" table is not really a dimension table since it contains ever permutation of sales person vs. customer. If I were to connect it to the main fact table by creating an intermefiary key (e.g. sales person or customer), won't I just end up back in the same situation?
 
Chihiro said:
Will a customer have multiple sales person that's assigned? If not, simple merge to fact table should suffice (filtered to "Y" only).

If not, you need to use nested CALCULATE() with FILTER, to take advantage of context transition.

Filters CAN Flow Up Hill – Via Formulas That Is - P3 Adaptive
Click to expand...

Thanks very much for your help. Yes there are multiple sales assignments in the table so a particular account might have several sales people showing "Yes". I've read that article...I thought that the CROSSFILTER function was supposed to allow for filters to flow in the other direction, so not quite clear about the distinction vs. this method.

I realise that I was misunderstanding my issue. I was using a CROSSFILTER measure to solve for my inability to add a functioning slicer based on a column on the "Many" side of a relationship. But the resulting slicer calculated different values (by filtering only the underlying Yes/No account data) rather than just toggling the view between Yes and No accounts.

So my real quesiton is - how to create a slicer that will toggle the results by category based on such a column, which is not in a dimension table??
 
CROSSFILTER is fine if only one directional calculation is needed. But if you need context transition within your measure. It's best to use CALCULATE with FILTER. It's relatively simple syntax and easier to follow direction change etc.

I'm not sure I get your question. I'd recommend uploading sample workbook.
 
You must log in or register to reply here.
Back
Top