How to find repetitive values

[Anonymous]

Hi,

I am trying to create a measure that can limit the number of hang up calls. Sth. like this:

if a Calling Party Nr repeates more than 5 times a day, set the # of hang up calls to 5, else set the current number.

How can I do that?

Thanks!

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Pls test the below:

test = 
VAR test1 =
    CALCULATE (
        SUM ( 'Table'[# of hang up calls] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Calling Party NR] = MAX ( 'Table'[Calling Party NR] )
        )
    )
RETURN
    IF ( test1 >= 5, 5, MAX ( 'Table'[Calling Party NR] ) )

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[danextian]

Hi @Anonymous 

These measure below will count the number of hang up calls per number per date regardless of the filter context.

Count of Hang Up Calls per Nr and Date =
CALCULATE (
    COUNTROWS ( Table ),
    ALLEXCEPT ( Table, Table[Date], Table[Calling Party Nr] )
)

[johncolley]

Hi @Anonymous ,

Create the following measures:

Hang up calls = SUM('Table'[# of Hang up calls])

limitto5 = SWITCH(
TRUE(),
[Hang up calls] > 5, 5,
[Hang up calls] <=5, [Hang up calls]
)

If this solution solved your problem, please mark it as a solution!