December 12, 2017 at 3:01 pm
Hello, I have a tab cube, with a dimension key column I'd like to get a distinct count on with a single row result.
This column has 3 distinct values, with a lot of count behind each. I can T-SQL the column with Count and Distinct(count(... and get the results I'm looking for, however when I try it in the SSAS Tab Fx bar, I can only use COUNT to get the total rows of a straight count, or with DistinctCount I get 3. I can't seem to get the right syntax on the Fx bar to simulate what I can get in T-SQL:
SELECT distinct(count(Dim_Key)) as "DCount"FROM [dbo].[ViewName]
In SSAS Tab Cube Fx:
[Distinct Count]:=(count('TableName'[Dim_Key]))
And this gives me what I want, but I don;t think the number is distinct.
Do I need to create another column first, populate it, then pull from it a distinct count? If so, how best?
Thanks
December 12, 2017 at 3:22 pm
In your example, the distinct(count(...)) is really nothing other than a count. The count() function returns one distinct value (the count of records), and unless used in conjunction with other attributes in your table the distinct keyword has no influence.
December 12, 2017 at 3:51 pm
Right, but how about the Fx in the tab cube on the column, there is a difference between count and distinctcount functions, and is my focus, needing distinct count off the column in the cube
December 12, 2017 at 4:10 pm
quinn.jay - Tuesday, December 12, 2017 3:51 PMRight, but how about the Fx in the tab cube on the column, there is a difference between count and distinctcount functions, and is my focus, needing distinct count off the column in the cube
The count() function in DAX will give you the equivalent. The distinctcount() DAX function returns the number of distinct values in a column. From your example, it looks like you just need a count.
December 12, 2017 at 5:20 pm
I think your right, as I tested around in comparing t-sql and Excel to the test cube with Dax count. Though a dim key col may not be optimal, tomorrow I’m going to test it on an account column, one I know can be spread across but broken when filtered with other columns, and validate the accounts ‘count’ to verify it’s distint and properly reduced in number, as I think this test to be sound. I’m trusting it’s good as Dax and tab cube is smart enough to know this is what’s desired.
December 13, 2017 at 9:39 am
quinn.jay - Tuesday, December 12, 2017 5:20 PMI think your right, as I tested around in comparing t-sql and Excel to the test cube with Dax count. Though a dim key col may not be optimal, tomorrow I’m going to test it on an account column, one I know can be spread across but broken when filtered with other columns, and validate the accounts ‘count’ to verify it’s distint and properly reduced in number, as I think this test to be sound. I’m trusting it’s good as Dax and tab cube is smart enough to know this is what’s desired.
So the best on a few dim_key columns I can do a distinct(count()) on, and others only Count() in the Dax Fx field to accurately get the counts I want, that splay out to the same as what I can do with T-SQL as a backcheck. This is as good as I can get or expect. And for the odd one, I'm doing both Count and DistinctCount added measures as a compromise.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply