July 6, 2016 at 4:04 pm
Relatively new to DAX in Tabular SSAS.
Looking for a way to count distinct items that have to meet a LEN criteria.
Something like this
=IF(LEN([COLUMN1] > 4), DISTINCTCOUNT([COLUMN1], 0)))
I did concatenate two fields into a calculated column so that piece is good, but sometimes I have only one of the two values that pulls through and I want to omitted those, that's why I am trying to code with LEN.
Any thoughts would be greatly appreciated.
Querying Microsoft SQL Server 2012/2014 - Certified
July 6, 2016 at 4:48 pm
patrick.w.wheeler (7/6/2016)
Relatively new to DAX in Tabular SSAS.Looking for a way to count distinct items that have to meet a LEN criteria.
Something like this
=IF(LEN([COLUMN1] > 4), DISTINCTCOUNT([COLUMN1], 0)))
I did concatenate two fields into a calculated column so that piece is good, but sometimes I have only one of the two values that pulls through and I want to omitted those, that's why I am trying to code with LEN.
Any thoughts would be greatly appreciated.
The only thing that seems incorrect in your formula is the order of operations. This may work better:
=DISTINCTCOUNT(IF(LEN([COLUMN1] > 4), [COLUMN1], null))
The above assumes that a null is not included in a distinct count, which is usually true for T-SQL but you should verify in DAX. It may also be possible to use "nothing" instead of "null".
July 7, 2016 at 9:47 am
Martin thanks for following up. The order of operations make sense, almost like an aggregate on the outside of a case statement, however I was still unable to get the formula to work.
The calculated column is a concatenation of a ID and a Date. There will also be an ID but not always a date, I want to omit the date from the distinct count.
Date can look like this
4677
7899
764509/09/2016
3355
etc....
The first 4 or sometimes 5 or 6 is the unique id which will always be there and the other concatenated code will be counted. Len will have the be > 7 to account for potential 6 digit ID's. The ID is sometimes the same that's why I am building that column with the date as well.
Thanks in advance.
I could go to the table and build the concate column together at the table/view level.
Querying Microsoft SQL Server 2012/2014 - Certified
July 7, 2016 at 9:54 am
patrick.w.wheeler (7/7/2016)
Martin thanks for following up. The order of operations make sense, almost like an aggregate on the outside of a case statement, however I was still unable to get the formula to work.The calculated column is a concatenation of a ID and a Date. There will also be an ID but not always a date, I want to omit the date from the distinct count.
Date can look like this
4677
7899
764509/09/2016
3355
etc....
The first 4 or sometimes 5 or 6 is the unique id which will always be there and the other concatenated code will be counted. Len will have the be > 7 to account for potential 6 digit ID's. The ID is sometimes the same that's why I am building that column with the date as well.
Thanks in advance.
I could go to the table and build the concate column together at the table/view level.
Are you able to split the unique id from the date? If so, I'd recommend that you create a derived/calculated field with just the unique id. Your formula would then be a simple distinct count over the unique id field.
July 7, 2016 at 12:36 pm
Here is what I did 😀
Not proud......
Added this to my fact table to create two concatenated columns to reference
,CASE WHEN LEN(CONVERT(VARCHAR, CONVERT(INT, CONVERT(CHAR(4),SUBMISSION_ID))) + CONVERT(VARCHAR, CONVERT(DATE, CONVERT(CHAR(10), ORIG_RCVD_DT, 112)))) > 5
THEN CONVERT(VARCHAR, CONVERT(INT, CONVERT(CHAR(4),SUBMISSION_ID))) + CONVERT(VARCHAR, CONVERT(DATE, CONVERT(CHAR(10), ORIG_RCVD_DT, 112)))
ELSE ''
END AS UID_ORIGINAL_DATE
,CASE WHEN LEN(CONVERT(VARCHAR, CONVERT(INT, CONVERT(CHAR(4),SUBMISSION_ID))) + CONVERT(VARCHAR, CONVERT(DATE, CONVERT(CHAR(10), FINAL_LOAD_DT, 112)))) > 5
THEN CONVERT(VARCHAR, CONVERT(INT, CONVERT(CHAR(4),SUBMISSION_ID))) + CONVERT(VARCHAR, CONVERT(DATE, CONVERT(CHAR(10), FINAL_LOAD_DT, 112)))
ELSE ''
END AS UID_LOAD_DATE
To account for the '' cells I went ahead and did a -1 to remove those since they are rolled up / distinct NAME:=DISTINCTCOUNT([COLUMN1]) - 1
It works........ That's about all I can say.
Thanks for suggesting ideas, I appreciate that a lot.
Querying Microsoft SQL Server 2012/2014 - Certified
July 7, 2016 at 12:45 pm
No worries. Instead of returning an empty string ('') in your case statement, consider returning a null...I believe that nulls are excluded from distinct counts and you would therefore not need the -1. Test it first though...
July 7, 2016 at 1:08 pm
Sounds good I'll do that. Makes sense.
Querying Microsoft SQL Server 2012/2014 - Certified
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply