Forum Discussion
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 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
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.
9 Replies
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!
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.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
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.
Hi datanau001
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
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
Best to say initially that you want this for a slicer so you get the right answer straight away.
regards
Phil
