Forum Discussion
Advocate IIHow to remove duplicates in calculated column?
I want to remove some duplicates from a calculated column. I want to use this column in a measure in LookupValue() but I get an error "a table of multiple values was supplied where a single value was expected." Upon studying the calculated column I got to know that some rows have duplicates which prevent me from getting the max value in the lookupvalue function.
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
18 Replies
BeemsCResolver III
What formula did you use for the column?
Zain26Occurence =
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.- Ashish_Mathur
Super User
Hi,
In a spare column, please indicate the values that you are expecting.
- Zain26
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 24BANK 3pm-6pm 54
STORE 9pm-12am 96
STORE 12pm-3pm 34
STORE 6pm-9pm 78STORE 3pm-6pm 10
SCHOOL 9am-12pm 5
SCHOOL 12pm-3pm 120
SCHOOL 6pm-9pm 1SCHOOL 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 SCHOOLPeakTime 9pm-12am 9pm-12am 12pm-3pm
- Ashish_Mathur
Hi,
For Bank, there are two time period against the maximum "CountofTimeRange". So the answer should be 9pm-12am,3pm-6pm. Am i correct?
- Zain26Yes. However if I could get only 9pm-12am, that would be okay as well.
- Zain26
I am pretty new to powerBI so I'd be really thankful for your help.
- Zain26
I have other measures as well in the Matrix would the filter affect them too? Also please advise a few.
Also please tell about how to deal with the error?
