Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PhilSmith
Helper I
Helper I

Count or CountDistinct in Row gives count for all rows in each row.

I am not sure quite how to word this.  This problem is showing up for every visual using this field, and measures as well, but this is the easiest way to see where something is wrong.

A basic table visual, showing results from two related tables.  Sales, one order per row, and CustomerNo, one per order.

 

If my table consists of Order#, CustomerNo, I get exactly what I would expect.  Each order number, and each CustomerNo that goes with that order number, one per line.

 

If I change CustomerNo to COUNT of CustomerNo, just using the visual's built in function, I get the total count for all CustomerNos on each and every line.  There is only one per row in the dataset, but I am getting 194,000 per line.

 

Now my ultimate goal is not "Per order,", but based on a date range, Sales territory, etc .  It doesn't matter, I get a count of ALL customers.  However, the relationship is there., as the instance that displays without a count works just fine.  Filters do not affect the total number.  Build a measure to do the counting, same results.  

Where should I be looking?

 

Phil

 

1 ACCEPTED SOLUTION
ebeery
Solution Sage
Solution Sage

Hi @PhilSmith ,

 

It's hard to say without looking at your model, but to me this sounds like a relationship issue.

 

Try setting the crossfilter direction set on your relationship between the sales and customers tables to "Both" (it is probably at single by default).

 

image.png

 

Note that this is NOT a recommended practice long-term, but it can help us diagnose what's going on.  If that fixes the issue, then you should set the filter direction back to "Single" and develop your COUNT measure using a CROSSFILTER calculate modifier to temporarily set the filter direction between those tables to BOTH.

 

The issue is that in your current scenario, you are effectively asking the "Sales" table to filter the "Customer" table, which it cannot do due to the filter direction.

 

https://docs.microsoft.com/en-us/dax/crossfilter-function

View solution in original post

1 REPLY 1
ebeery
Solution Sage
Solution Sage

Hi @PhilSmith ,

 

It's hard to say without looking at your model, but to me this sounds like a relationship issue.

 

Try setting the crossfilter direction set on your relationship between the sales and customers tables to "Both" (it is probably at single by default).

 

image.png

 

Note that this is NOT a recommended practice long-term, but it can help us diagnose what's going on.  If that fixes the issue, then you should set the filter direction back to "Single" and develop your COUNT measure using a CROSSFILTER calculate modifier to temporarily set the filter direction between those tables to BOTH.

 

The issue is that in your current scenario, you are effectively asking the "Sales" table to filter the "Customer" table, which it cannot do due to the filter direction.

 

https://docs.microsoft.com/en-us/dax/crossfilter-function

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.