Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to find repetitive values

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!

 

 

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

3 Replies

  • johncolley's avatar
    johncolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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!

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    4 years ago

    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] )
)
  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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