locked
Grouping based on user selection DAX query in SSAS Tabular model RRS feed

  • Question

  • Hello,

    I'm trying to write a user query that groups based on user selection. Following is a mockup of the data:

    ID Name Quadrant Qty
    1 A East 1
    1 A East 4
    2 B East 2
    3 C East 3
    4 D East 4
    2 B West 2
    2 B West 3
    3 C West 3
    1 A North 1
    2 B North 2
    3 C North 5
    3 C North 3
    1 A South 1
    2 B South 2
    3 C South 3
    4 D South 5
    4 D South 4

    I'm trying to get a sum of distinctcount of users per quadrant, but quadrants to be counted is driven based on the user selected. So for instance if all Quadrants (East,West,North,South) are selected the results should be as below:

    ID Name Quadrant Qty SUM_DistinctCount
    1 A East 1 13
    1 A East 4 13
    2 B East 2 13
    3 C East 3 13
    4 D East 4 13
    2 B West 2 13
    2 B West 3 13
    3 C West 3 13
    1 A North 1 13
    2 B North 2 13
    3 C North 5 13
    3 C North 3 13
    1 A South 1 13
    2 B South 2 13
    3 C South 3 13
    4 D South 5 13
    4 D South 4 13

    If just one quadrant say "East is selected then the Sum_DistinctCount should be 4 for all rows.

    Is there a way to accomplish this using DAX?

    I have the DAX query as below, which does just a sum of distinct count.  how can this query be modified so that the dataset to be grouped is determined based on the user selection

    =SUMX(SUMMARIZE(Table1,Table1[Quadrant],"TotalQty", DISTINCTCOUNT(Table1[ID])),[TotalQty])

    I have tried to use CALCULATETABLE(Table1, ALLSELECTED(Table1[Quadrant]) as the first parameter to the summarize function above but that does not help. Does anyone has any suggestions/ideas to get this working?

    Thanks!!


    • Edited by nihcas7713 Wednesday, September 30, 2015 10:59 PM added data mockupkupdata
    Wednesday, September 30, 2015 10:25 PM

Answers

  • Hi Nihcas7713,

    According to your description, you need to add a calculated column to return the distinct count for each quadrant in your PowerPivot data model, right?

    If that is the case, you can DISTINCTCOUNT function to achieve this requirement. In DAX, DISTINCTCOUNT function counts the number of different cells in a column of numbers. I have tested it on my local environment, the sample DAX expression below is for you reference.
    =CALCULATE(DISTINCTCOUNT(Test1001[ID]),ALLEXCEPT(Test1001,Test1001[Quadrant]))

    Reference
    https://support.office.com/en-us/article/DISTINCTCOUNT-Function-DAX-9925bd2d-ff12-4d51-9e1a-0ecaaabda1f3?ui=en-US&rs=en-US&ad=US

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Friday, October 30, 2015 6:56 AM
    • Marked as answer by Charlie Liao Wednesday, November 4, 2015 8:02 AM
    Thursday, October 1, 2015 2:21 AM