Countrows on a crossjoin "visual table"

[powerpivotdaniel]

I have this DAX code that generates a visual table. It is a customer table that is crossjoin with a date table so I will be able to count the customerbase for each date. In the customer table there are only one line per customer with a fromdate and todate.
How can I perform a distinct count on date and if I use this as a measure will I be able to use this measure combined with other tables in a pivot table etc. the date table.
Hope it make sense.


EVALUATE
(
FILTER (
CROSSJOIN (
FILTER ( 'Customer', Customer[CompanyName] = "IT CLARITY" ),
VALUES ( 'Date'[date] )
),
Customer[ValidFrom] <= 'Date'[date]
&& Customer[Validto] >= 'Date'[date]
)
)

 

[Salle]

Hello,
If I understand you correctly you want to count distinct customers returned by the table expressions generated by the outermost Filter function?
You can use the SUMMARIZE function to return a table expression with distinct values and COUNTROWS to count the rows of that table:

COUNTROWS(
SUMMARIZE(

FILTER (
CROSSJOIN (
FILTER ( 'Customer', Customer[CompanyName] = "IT CLARITY" ),
VALUES ( 'Date'[date] )
),
Customer[ValidFrom] <= 'Date'[date]
&&Customer[Validto] >= 'Date'[date]
),
Customer[CompanyName]))

Hopefully i understood your problem correctly

 

[powerpivotdaniel]

The problem is actually fairly complicated.
I would like to count how many customer there are for every date.
The customer table only have one line per customer with a fromdate and todate. Which is the period a customer is active.
The table can look like this,

Customer Activefrom Activeto
XX 2015-01-01 2015-02-01

This customer is only active between those two dates, so if I filter the dates in the pivottable to 2015-01-20, this customer will count as 1. But if I filter the dates to 2015-04-01 then the customer will not count as 1.
The problem is easy to do in SQL with a crossjoin between the customertable and the datetable and then load that table to the tabular.
I have no idea if this can be done in DAX alone?

 

[powerpivotdaniel]

Hey Salle,
You were right. I got what wanted a list of dates and the distinct number of customer for each date (I just switch the Companyname with date). It works in DAX studio, but It dosent work in the tabular model. I got this message "An item with the same key has already been added" What does that mean?

 

[Salle]

Hello,
Yes, you put the values you want to count as distinct in the second argument in the SUMMARIZE function.
Unfortunately I haven't worked with tabular model, is that in SSAS? Perhaps it has got something to do with using the same columns in multiple places?
That would however be very strange to not being able to do so...
Do you get the same error when evaluating the expression without COUNTROWS and SUMMARIZE?

 

[powerpivotdaniel]

Do you work with Power Pivot then?
What I dont get is- when you use summarize you end up with a table of all the dates and the number of customers for those dates. How can you be able to use a table as a measure (I thought that a measure can only be one number). And how does this summarize table relate to the other table in the model?

 

[Salle]

Yes, PowerPivot.
You are right in that a measure must be a number. Thats why the SUMMARIZE function must be wrapped in a COUNTROWS function who returns that number.
Your outermost FILTER function defines a table expression that has been generated by a CROSSJOIN (also a table expression) and ultimately filtered in the second argument.
That table expression is passed to the SUMMARIZE function in the first argument.
It is in the second argument, which is a, "group by" argument, where u define what u want to use from the table expression in the first argument.
Since it is a group by argument it will return unique values, just like GROUP BY in SQL.

It relates to the other tables in the model in that sense that it evaluates the table expression that you define in the first argument.
For example, if you would write ALL('Date'), you would get a table expression consisting of all rows in the Date table.
So it comes down to what table expression you pass to the first argument in SUMMARIZE and what column(s) you want to count as distinct, in the second argument.

Hope that clarifies!