Post reply

Groupby in DAX

  • July 11, 2016 at 2:31 pm

    #328115

    So........

    My previous post I ended up getting down to the month year level, but to drill down to the supplier level I am forced to do a case statement to row count for each record. I did that in SQL and pulled it into my fact table in SSAS. (Tabular)

    My problem is a left outer join, so in some cases the UI is duplicated. I am trying to group by in SSAS in the calculated column to get the true count.

    I have two columns one with Unique ID's which is duplicating along with the second part the rowcount column.

    1st column is 4 digits 1234 e.g. and the second column is either 1 or 0 for the counter. I'd like to distinctcount on a group by but not to familiar with DAX. I'll keep Googling but thanks in advance!

    Querying Microsoft SQL Server 2012/2014 - Certified

  • July 11, 2016 at 2:50 pm

    #1889519

    You may need to use SUMMARIZE. Think of it like the DAX equivalent of a summary query in T-SQL... Complete with GROUP BY etc.

    Here it is from the MS Website.

  • July 12, 2016 at 11:56 am

    #1889685

    I ended up creating another calculated columns in SSAS Tab and then did a distinct count, the other problem now is when I do my breakout for one to one using the pivot table tool from visual studio in SSAS it doesn't show the break down.......

    It rolls up to year month no problem, but the break down for supplier doesn't tie one to one. I bet I am missing something simple here, but right now it's sailing over my head.....

    Ugh......

    Querying Microsoft SQL Server 2012/2014 - Certified

  • July 13, 2016 at 12:54 pm

    #1889945

    Ended up using calculate filtering on the false value. Worked fine, just took a while to get the syntax down.

    Querying Microsoft SQL Server 2012/2014 - Certified

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply