Forum Discussion
How 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
Hi guys, I am trying to get the most repeated value in a column grouped against another column.
USER | TimeRange | CountofTimeRange|
BANK 9pm-12am 54
BANK 6pm-9pm 24
BANK 6pm-9pm 24
BANK 3pm-6pm 54
STORE 9pm-12am 96
STORE 12pm-3pm 34
STORE 6pm-9pm 78
STORE 3pm-6pm 10
SCHOOL 9am-12pm 5
SCHOOL 12pm-3pm 120
SCHOOL 6pm-9pm 1
SCHOOL 3pm-6pm 2
==================================
I want to get the value 9pm-12am against th BANK column, in a row using matrix visualization. and ignore the lesser time range. However when I attempt to do this in a measure using
VAR peaktime= Calculate(LookupValue([Timerange],[CountofTimeRange],MAX([CountofTimeRange])),[Date].[month]>= DATE(maxyear,maxmonth,1))
receive an error which says
A table of multiple values was supplied where a single value was expected.
Using the Matrix visual the result should be something like this,
BANK STORE SCHOOL
PeakTime 9pm-12am 9pm-12am 12pm-3pm
Hi,
For Bank, there are two time period against the maximum "CountofTimeRange". So the answer should be 9pm-12am,3pm-6pm. Am i correct?
- Yes. However if I could get only 9pm-12am, that would be okay as well.
Hi,
I think i have solved it. See the image below. Is that the result you are expecting?
You posted a similar question here. Now please tell me which of the two questions is your actual one - the one in this thread or the other one. The major difference between both datasets is that the one in the other link does not have any count column (you have inface created that in a calculated column). So, my question is that in your base data, will you actually have a count column or no.
I have also been able to solve the probelm where you do not have a count column. See imag below
- Hi Ashish, yes you have solved it. The actual data doesn't have a count column, I have created a calculated column to get the count. Please share the formula with which you solved this.