Show only rows that have sum > 0 each month?

[Anonymous]

Hi, hopefully someone can help me with this.

I have a table of data that shows for each user, how many times they logged onto a system per month. It is structured like the below.

Name         Jan       Feb      Mar     Apr    May

Bob             0            1         2          1        0

Ann             1            1         4          2        1

Joe              2            5         5          1        1

What I want to do is filter this table to show only the users who have more than 1 login each month. So from the above example I would only want to see Ann and Joe as they logged on each month if that makes sense?

Thanks for any help!

[AnthonyTilley]

hi @Anonymous 

just to be clear is your data stored in the format below or is this a representation of the matrix visual you are using.

just need to be clear if in your model you have 5 colunms nmaed Jan-Feb-Mar-Apr-May or do you have something like below 

Month	Name	loggins
Jan	BOB	0
Feb	BOB	1
Mar	BOB	2
Apr	BOB	1
May	BOB	0
Jan	Ann	1
Feb	Ann	1
Mar	Ann	4
Apr	Ann	2
May	Ann	1
Jan	Joe	2
Feb	Joe	5
Mar	Joe	5
Apr	Joe	1
May	Joe	1

also if it is as above do you have a row when the user is zero or is there just no row of data.

 if you could provide the exact layout of your data that would help alot 

[Anonymous]

Hi, yes the data is stored like the above and a row appears even if the user is zero.

Thanks!

[AnthonyTilley]

And finally do you wan to be able to select a range within you months and have the users removed if any single month is zero 

or are we saying that if at any point in the data a user has a loggin value of 0 then to exclude them even if it is not in the displayed range of values 

fore example using your provided table if you where to filter the table to only show FEB MAR APR would you wan tall three user to show.

if there is no requirment to be able to filter by month then you can apply the following calculated colunm

Column = 
var n = 'Table'[Name]
var is0 = CALCULATE(COUNTA('Table'[Name]),all('Table'),'Table'[Name] = n, 'Table'[loggins]=0)
var ret = if(is0 > 0 , TRUE, FALSE)
return ret

this will give you a colunm that will display True if the user has any 0 values and faluse if there is no 0 values 

this can then be used in a filter or slicer to remove users with 0 values 

if however you need to only filter based on a selected range then this becomes a littel more difficult 

[AnthonyTilley]

If you do require to beable to filter the month then use the following Measure for your values

Measure = 
var n = max('Table'[Name])
var s = sum('Table'[loggins])
var is0 = CALCULATE(COUNTA('Table'[Name]),ALLSELECTED('Table'),'Table'[loggins] = 0,'Table'[Name]=n)
var check = if(is0 > 0 ,blank(),s)
return check

this will find any user within the selected values that contains a 0 in any given month and replace all their values with blank which will remove them from the visual 

example below when all months are selected then only Ann and joe are shown as Bob has zeros but if we filter the table to only Feb, Mar, Apr using a slicer then Bob is back in as he now has no zeros 

[Anonymous]

Hi, thanks for this but it is giving me an error about using MAX on value string?

[AnkitBI]

This is how we can achieve in Power Query.

1) #"Changed Type1" is your source table.

2) ChangeToList - Convert each row to list after skipping Name Column

3) Only Select Rows for which there is no '0' values. You can change to search for any value here (each _ < 1)

    ChangeToList = Table.AddColumn(#"Changed Type1", "Custom", each List.Skip(Record.FieldValues(_))),
    SelectRowsWithNoZeroes = Table.SelectRows(Table.AddColumn(ChangeToList, "Has0Value",each if(List.MatchesAny([Custom],each _ < 1) ) = true then "Yes" else "No"),each [Has0Value] = "No"),

 Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.