Forum Discussion

Zain26's avatar
Zain26
Icon for Advocate II rankAdvocate II
8 years ago
Solved

How 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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

Zain26's avatar
Zain26
Icon for Advocate II rankAdvocate II
8 years ago

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. 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

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