DISTINCTCOUNT measure question RRS feed

  • Question

  • Hi,

    I've a PowerPivot model with the Fact_Connections containing: ID, User_ID, Network_ID, ConnectionDateTime and ConnectionDate (with time equals to 00:00:00). I've created the ConnectionDate to create a relation between a Time table (date, year, month, day, week,...) and the Fact_Connections table. I've also a relation between the Users table and the Fact_Connections and a relation between the Networks table and the same fact table. I can have more records in the Fact_Connection for the same user and the same network, but as an exception the same user could connect to a different network. Depending on time, I need to calculate the number of active connections (active connection in the year, or in the month or in day). So, I've created this measure:

    CALCULATE(DISTINCTCOUNT(Fact_Connections[User_ID]); Filter(Fact_Connections; MAX(Fact_Connections[ID])))

    Using MAX(Fact_Connections[ID]) I don't need to use LASTDATE for ConnectionDate obtaining an error for date duplication.

    In the pivot table I've added three slicers, one for the year, one for the month and the last one for the day.

    This measure is rightly calculated for my sample data, but when I add the Network as a slicer or a row label, I can see two rows one for each network connected by the single user with 1 as the measure value, and the total equals to 1 and not 2. The right value for the total is 1, but I'd like to have zero for one of the network or see only one row.

    I've tried to change the measure definition in order to define a table with only the rows having the max id of the fact table for each user.

    Any suggests to me, please? Thanks

    Is it possible to move this thread to the SQL Server PowerPivot for Excel, please?

    • Edited by pscorca Friday, January 11, 2013 11:11 PM wrong posting
    • Moved by Elvis Long Wednesday, January 16, 2013 8:19 AM correct forum
    Friday, January 11, 2013 1:45 PM


All replies