Get count per timestamp for each ID

[datanau001]

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 showing the value 2 and so on.

However, I'm not sure how to create this column.

Would you please help me with this question?

Thank you 

Marcelo

[PhilipTreacy]

@datanau001 

Best to say initially that you want this for a slicer so you get the right answer straight away.

regards

Phil

[JamesHowell]

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!

[edhans]

Try this @datanau001 - note: this is a measure, not a column. Measures are best practices.

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

If you need further help, please give us some data to work with. Cannot use images to paste into Power BI.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[datanau001]

Hello Edhans,

Thank you for your quick reply.

Your suggestion works, however, I can't use it as a slicer.

What would be a similar solution but that could be used as a slicer?

Br//

Marcelo

[edhans · Accepted solution]

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.

[datanau001]

Hello Edhans,

Exactly what I was looking for.

Thank you very much for the support.

Regards

Marcelo

[edhans]

Glad I was able to assist @datanau001 

[PhilipTreacy]

Hi @datanau001 

Download sample PBIX file

Does it have to be a column?  Perhaps a measure would be better?

Time Stamp Incidence = CALCULATE(COUNTROWS('Table1'), FILTER(ALL('Table1'), 'Table1'[SR_Number] = SELECTEDVALUE('Table1'[SR_Number])))

regards

Phil

[datanau001]

Hello Phil,

Thank you for the quick reply.

Creating a measure would work but I'll not be able to use it as an slicer. That's why I think a column would work better. 

What do you think?

Regards

Marcelo