Forum Discussion
Advocate IIHow to remove duplicates in calculated column?
Hi Zain26,
Create measures like below:
Max Count = CALCULATE ( MAX ( 'TimeRange Table'[CountofTimeRange] ), ALLEXCEPT ( 'TimeRange Table', 'TimeRange Table'[USER] ) ) Peak Time Range = CALCULATE ( LASTNONBLANK ( 'TimeRange Table'[TimeRange], 1 ), FILTER ( 'TimeRange Table', 'TimeRange Table'[CountofTimeRange] = [Max Count] ) )
Add field [USER] into Columns area of matrix, and add measure [Peak Time Range] into Values section.
Best regards,
Yuliana Gu
Advocate IIOccurence =
VAR latestDateTime= MAXA('Comments'[Time])
VAR year= YEAR(latestDateTime)
VAR month= MONTH(latestDateTime)
VAR c= CALCULATE(COUNTROWS('Comments'),ALLEXCEPT(Comments,Comments[Intervals],Comments[CommentIdentifier]),Comments[Time].[Date]>=DATE(year,month,1))
return c
Here Intervals is another calculated table where I have defined the time ranges using my Time column.
Basically I want to get the Interval value against max time for each Comment Identifier. But there are duplicates as you can see for Interval 3pm-6pm Occurance is 40, and for 9pm-12am is also 40.
Advocate II
This is how I want my matrix to look like. If you look at the Status Peak , that is how I expect to get the value for CommentP. In simple words I expected to take the most repeated value in the Interval column. Upon to do that I tried this approach of getting count for each [Interval] against the [Comment Identifier]. And then look for the max count and retrieving the corresponding [Interval]
- Ashish_Mathur
Super User
Hi,
I am still not clear. With respect to the data that you have shared, please show how you want your matrix visual to be (with actual entries in rows/column/values)
- Zain26
The image which I shared is exactly the way I want my visualization to be. The [CommentIdentifier] is the columns while all the measures are the values which are showed on rows.
I intend to create a measure to calculate the most repeated value of [Interval] for each [CommentIdentifier] and then add it to the matrix as a value and then show it on row using the provided functionality.
This is how I would like the value Comment Peak to be. Currently I have filled it with dummy values. The actual values would vary for each column.
- Ashish_Mathur
