How to remove duplicates in calculated column?

[Zain26]

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. 

[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       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

[v-yulgu-msft · Accepted solution]

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

[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?

[Zain26]

Yes. However if I could get only 9pm-12am, that would be okay as well.

[Ashish_Mathur]

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

[Zain26]

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.

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[Ashish_Mathur]

Hi,

Please give me time until tomorrow to share my solution.

[chethan]

@Zain26 put the filter Visual Level filter it will work so that you can ignore the lesser time

[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? 

[Zain26]

I am pretty new to powerBI so I'd be really thankful for your help.  

[BeemsC]

What formula did you use for the column?

[Zain26]

Occurence =
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. 

[Zain26]

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]

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]

Hi,

You posted a similr question here as well.  See my reply there.

[Ashish_Mathur]

Hi,

In a spare column, please indicate the values that you are expecting.