Forum Discussion

datanau001's avatar
datanau001
Icon for Helper III rankHelper III
5 years ago
Solved

Get count per timestamp for each ID

Hello all,   I'm trying to a new column called "Time Stamp Incidence" to show the incidence of timestamps per each ID(SR_Number). See the below screenshot: 1st ID on the new column would be showi...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    Yes datanau001 - one of the few times a column is better than a slicer. Slight tweak to my measure:

     

     

    Item Count = 
VAR varCurrentValue = 'Table'[Item]
VAR Result = 
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[Item] = varCurrentValue,
        REMOVEFILTERS('Table')
    )
RETURN
    COALESCE(Result,0)

     

    Strictly speaking the COALESCE() at the end isn't necessary as you'd never have a blank like this in a column, but I am just in the habit of using it with any measure that has COUNTROWS() so I get a true 0 vs <BLANK>

    You could just use "Result" at the end after the RETURN statement.

JamesHowell's avatar
JamesHowell
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Hey Marcelo

 

If you want to use as slicer then a column would be necesary. From a performance perspective it would be better to do this step in Power Query before loading into your data model. See this solution from MarcelBeug on the forum which will give you the result you need Solved: count distinct on column in power query - Microsoft Power BI Community.

It's effectively 2 steps, Group By with a Distinct Count on your SR_NUMBER and then expanding again. I did a quick test and would get the below result:

Good luck!