locked
Distinct count Id in fact RRS feed

  • Question

  • Hi,

    I'm trying to make a distinct count in the fact on one of the dimensions Id's, dimension User.

    And the results thats returning this:

     

    But when i run in sql, the results that i wanted is this:

    I create a measure, a distinct count on UserId in fact and the relationship selected type is "Fact".

    Is there some configuration  that I didn't done ?

      

    Tuesday, September 24, 2019 8:22 PM

Answers

  • Hi Caio,

    Thanks for your detailed description.

    It sounds there is no problem in these operation that you mentioned. So what is the expression for the measure column "UserId"?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 26, 2019 8:03 AM

All replies

  • Hi Caio R,

    Thanks for your post.

    Per the existing information, it is hard for me to provide certain solution. But for the first picture, why are the values of column "User Id" same as the values of column "Total User Id"? It is very strange, could you please show us how to get such results in more detail? I guess you are binding wrong field.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 25, 2019 2:08 AM
  • Hi Will,

    Thanks for answering.


    I'm trying to get a distinct count from dimension User in fact Sales,  The colunm "Total User Id" shows the total users that exists.
    And the colunm (the measure) "User Id" it's a distinct count, so I used another colunm showing the Year-month 


    Follow the structure that i created:

    > Sales fact
    SaleId
    UserId
    DateId
            Value

    > User dimension
    UserId
    Name
    CompanyId


    > Date Dimension
    DateId
    Date
    Year-Month


    The binding of the tables and colunms:

    > Sales fact = UserId
    > User dimension = UserId

    > Sales fact = DateId
    > Date Dimension = DateId


    Measure bind:

    I selected the field "UserId" from "Sales Fact" and created the measure a distinct count with the same name "User Id". 

    Type relationship = Fact

    > Sales fact = UserId
    > User dimension = UserId


    • Edited by Caio R Wednesday, September 25, 2019 1:45 PM
    Wednesday, September 25, 2019 11:58 AM
  • Hi Caio,

    Thanks for your detailed description.

    It sounds there is no problem in these operation that you mentioned. So what is the expression for the measure column "UserId"?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 26, 2019 8:03 AM
  • Hi Will,

    The issue that i get and resolved, it was the process of creating the measure.

    I had to remove the dimension first, create the measure and at last reference the dimension in the cube.

    Process separately the dimension and then the cube. And it work! 

    Thank you Will

    Friday, September 27, 2019 3:53 PM