Need Help Writing A Formula

[MikeMCFRS]

Hello All,

I  would appreciate any ideas from the community to help me figue out how to write an appropriate formula for the scenario below.
I have data that has the following columns: incident number (Based on Calls to a department),date, and Names. I have been able to create a table with the number of calls per month by name( e,g Wifred ,Jan (20), February (50) etc.

What I want to create next is a formula that says: if Name(x) has not called three time in a 3 consecutive months then remove or do not show or edit the name from the list.  

Also, Is it possible to write a formula to show only the top 10 highest calls or I just use chart to shaow that.

Your ideas are welcome. Many thanks in advance.

[MikeMCFRS]

Thanks to you all for helping me resolve the formual issue. I have tested it to see how it turns out with additional data.

I have a calculated column for AGE based on the DOB and I wanted to show the ages in ranges such as 0-10, 11-20, 21-30 etc.

Any ideas?? I have reviewed some possible answers from the forum but doesnt quite fit what I am looking for. I also used bins but it cpatures them into 10, 20, 30, 40 etc which is not the result I want to display in pie chart.

Thanks

[v-joesh-msft]

Hi @MikeMCFRS ,

I'm a little confused by your description. Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MikeMCFRS]

@v-joesh-msft , Many thanks for offering the suggestiions of better ways to get help and laso your kind willingness to help.

I have prepared a sample data to be attached but cannot find that option with this reply email. I will repost and hopefully you can see it.

Thanks for your help so far.

[MikeMCFRS]

@v-joesh-msft , I have shared a link from dropbox containing the sample file. I have removed names from the sample which is due to privacy concerns. The names help you to see the number of calls made by each name by the month.

Let me know if you are able to access it.

As mentioned in my previous post, my goal is to write a formula that (a) will delete or remove any name that has not called in the last three(3) consecutive months from the table and (b) a formula that show the top 10 highest callers at a glaance. I hope this explains my end goal better. Thanks

   https://www.dropbox.com/sh/qpnznp6elendawc/AADZNV67vx6Jo-oJ6f73FHYna?dl=0

[Ashish_Mathur]

Hi,

Fill the Name column with dummy entries and reshare the download link of the PBI file.

[v-joesh-msft]

Hi @MikeMCFRS ,

Based on your sample data, I replaced some of the Name fields with some English words. I did some tests and the results are as follows, hope is the output you expect:

1. delete or remove any name that has not called in the last three(3) consecutive months from the table

Need to create two measures:

Last three month =
VAR _LASTDATE =
    CALCULATE ( MAX ( 'Data SUV'[Date] ), ALL ( 'Data SUV' ) )
RETURN
    VAR _VALUE =
        CALCULATE (
            COUNTROWS ( 'Data SUV' ),
            FILTER (
                'Data SUV',
                DATEDIFF ( 'Data SUV'[Date], _LASTDATE, MONTH ) >= 0
                    && DATEDIFF ( 'Data SUV'[Date], _LASTDATE, MONTH ) < 3
            )
        )
    RETURN
        _VALUE

Remove not called =
VAR _TABLE =
    CALCULATETABLE (
        SUMMARIZE (
            'Data SUV',
            'Data SUV'[Name],
            'Data SUV'[Date].[Date],
            "_VALUE", [last three month]
        ),
        ALLEXCEPT ( 'Data SUV', 'Data SUV'[Name] )
    )
RETURN
    IF (
        COUNTAX ( FILTER ( _TABLE, [_VALUE] <> BLANK () ), [_VALUE] ) = 3,
        COUNTROWS ( 'Data SUV' )
    )

The figure below is the result comparison：

2. show the top 10 highest callers at a glance

Need to create two measures:

Rank =
IF (
    [remove not called] <> BLANK (),
    RANKX ( ALL ( 'Data SUV'[Name] ), [remove not called],, DESC )
)

top10 =
CALCULATE (
    [remove not called],
    FILTER ( VALUES ( 'Data SUV'[Name] ), [Rank] <= 10 )
)

(The figure below shows only 3 rankings because I filled in fewer name fields)

Here is a demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ER21DiymeMBPlA86AU...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MikeMCFRS]

---

@v-joesh-msft wrote:

Hi @MikeMCFRS ,

Based on your sample data, I replaced some of the Name fields with some English words. I did some tests and the results are as follows, hope is the output you expect:

1. delete or remove any name that has not called in the last three(3) consecutive months from the table

Need to create two measures:

Last three month =
VAR _LASTDATE =
    CALCULATE ( MAX ( 'Data SUV'[Date] ), ALL ( 'Data SUV' ) )
RETURN
    VAR _VALUE =
        CALCULATE (
            COUNTROWS ( 'Data SUV' ),
            FILTER (
                'Data SUV',
                DATEDIFF ( 'Data SUV'[Date], _LASTDATE, MONTH ) >= 0
                    && DATEDIFF ( 'Data SUV'[Date], _LASTDATE, MONTH ) < 3
            )
        )
    RETURN
        _VALUE

Remove not called =
VAR _TABLE =
    CALCULATETABLE (
        SUMMARIZE (
            'Data SUV',
            'Data SUV'[Name],
            'Data SUV'[Date].[Date],
            "_VALUE", [last three month]
        ),
        ALLEXCEPT ( 'Data SUV', 'Data SUV'[Name] )
    )
RETURN
    IF (
        COUNTAX ( FILTER ( _TABLE, [_VALUE] <> BLANK () ), [_VALUE] ) = 3,
        COUNTROWS ( 'Data SUV' )
    )

The figure below is the result comparison：

2. show the top 10 highest callers at a glance

Need to create two measures:

Rank =
IF (
    [remove not called] <> BLANK (),
    RANKX ( ALL ( 'Data SUV'[Name] ), [remove not called],, DESC )
)

top10 =
CALCULATE (
    [remove not called],
    FILTER ( VALUES ( 'Data SUV'[Name] ), [Rank] <= 10 )
)

(The figure below shows only 3 rankings because I filled in fewer name fields)

Here is a demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ER21DiymeMBPlA86AU...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

@v-joesh-msft  Hi Friend, Many thanks for taking the time to help me through this sample. You got the concept correctly. Based on the results you tested and my own testing, I think I might need some mofidications with the formula.

From the picture, it seems to remove names that did not call in one month or the previous month. This I think did not cpature the 3 consecutive months concept. So for example, if a NAME did not call JULY, AUGUST & SEPTEMBER then that name should be removed. I am looking to identify high consistent callers for my department.

The top 10 callers was spot on but it seems to be based on the previous formula.

Thanks for your help and looking forward to your reply.

[v-joesh-msft · Accepted solution]

Hi @MikeMCFRS ,

You can try  the measure below:

remove not called =
VAR _TABLE =
    CALCULATETABLE (
        SUMMARIZE (
            'Data SUV',
            'Data SUV'[Name],
            'Data SUV'[Date].[Date],
            "_VALUE", [last three month]
        ),
        ALLEXCEPT ( 'Data SUV', 'Data SUV'[Name] )
    )
RETURN
    IF (
        COUNTAX ( FILTER ( _TABLE, [_VALUE] <> BLANK () ), [_VALUE] ) <> 0,
        COUNTROWS ( 'Data SUV' )
    )

Results are as follows：

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.