0

Due to performance issues I need to remove a few distinct counts on my DAX. However, I have a particular scenario and I can't figure out how to do it.

As example, let's say one or more restaurants can be hired at one or more feasts and prepare one or more menus (see data below).

enter image description here

I want a PowerPivot table that shows in how many feasts each restaurant was present (see table below). I achieved this by using distinctcount.

enter image description here

Why not precalculating this on Power Query? The real data I have is a bit more complex (more ID columns) and in order to be able to pivot the data I would have to calculate thousands of possible combinations.

I tried adding to my model a Feast dimensional table (on the example this would only be 1 column of 2 rows). I was hoping to use that relationship to be able to make a straight count, but I haven't been able to come up with the right DAX to do so.

Victor
  • 1,163
  • 4
  • 25
  • 45
  • What percentage of feasts have one menu and one restaurant? If it is a significant number then try this optimization: http://www.artisconsulting.com/blogs/greggalloway/2015/9/28/optimizing-large-distinct-counts-in-analysis-services-tabular – GregGalloway Mar 10 '16 at 01:27

1 Answers1

0

You could use COUNTROWS() combined with VALUES().

Specifically, COUNTROWS() will give you the count of rows in a table. That means COUNTROWS is expecting a table is input. Here's the magic part: VALUES() will return a table as results, and the table it returns are the distinct values in the table/column that you provide as the argument for VALUES().

I'm not sure if I'm explaining it well, so for the sample data you provided, the measure would look like this (assuming the table is named Table1):

Unique Feasts:=COUNTROWS(VALUES('Table1'[Feast Id]))

You can then create a pivot table from Powerpivot, and drag Restaurant Id into Rows, and drag the measure above into Values. Same result as DISTINCTCOUNT, but with less performance overhead (I think).

Jeremy Firth
  • 41
  • 4