HELP: Complaints Count of 3 or More

[Luke_Howells]

Hi everyone,

I am having a little trouble with something I am trying to do in PowerBI desktop. 

I am looking at a set of data that revolves arounds a set of complaints. The structure of the data contains the site name, date, status. The staus is basically if the complaint is open or closed. What I am trying to do is work out how many sites have complained more than 3 times over a certain time period. I am using a slicer to change the dates so this I am ok with. It seems to be an easy one but just can't work it out!!!!! 😠

I should note that each site is in the set of data multiple times

[Icey]

Hi @Luke_Howells ,

Create a measure like so:

Count of Site whose complaints count of 3 or more = 
SUMX (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[Site Name],
            "_CountofDate", COUNT ( 'Table'[Date] ),
            "_DistinctCountofSite", DISTINCTCOUNT ( 'Table'[Site Name] )
        ),
        [_CountofDate] >= 3
    ),
    [_DistinctCountofSite]
)

Best Regards,

Icey

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

[Luke_Howells]

Hi @Icey Thanks for the help. I should also mention that column Status contains blank records. Would it be possible to ignore these records?

[Icey · Accepted solution]

Hi @Luke_Howells ,

Try this:

Count of Site whose complaints count of 3 or more = 
SUMX (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[Site Name],
            "_CountofDate", CALCULATE (
                COUNT ( 'Table'[Date] ),
                FILTER ( 'Table', 'Table'[Status] <> BLANK () )    -------------------- edited
            ),
            "_DistinctCountofSite", DISTINCTCOUNT ( 'Table'[Site Name] )
        ),
        [_CountofDate] >= 3
    ),
    [_DistinctCountofSite]
)

Best Regards,

Icey

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

[Luke_Howells]

@Icey That's great thanks for your help 😄 

[amitchandak]

Use this with site name

sumx(filter(summarize((Table),Table[site],"_sum",count(table[Date])),[_sum]>3),[_sum])