## Desktop

Regular Visitor
Posts: 19
Registered: ‎09-19-2016

# Show all values ​​if in the last fortnight is different from 0

Hi People,

I have a table of cities and their values (hours) ​​per fortnight, with the following fields: "Depl City", "Fortnight" and "Hours".

I'd like to show in my table only the cities that contain values ​​(hours) other than "0" in the "last fortnight".

Can you help me?

Regards,

Renato França

Accepted Solutions
Highlighted
Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Show all values ​​if in the last fortnight is different from 0

[ Edited ]

@renatofrancavr  Okay I think we got this now!

You want to Show ONLY the Cities that have Hours in the Last Fortnight! So even if they had Hours in the previous fortnights showing in the Matrix  do not show them unless they have Hours in the last Fortnight!

So first I created this sample data in 'Table' with 3 Columns

Fortnight City Hours

 1/15/2017 Sao Polo 50 1/15/2017 Sao Polo 50 1/15/2017 Rio de Janeiro 50 1/15/2017 Rio de Janeiro 50 1/15/2017 Brasilia 50 1/15/2017 Brasilia 50 1/31/2017 Sao Polo 100 1/31/2017 Sao Polo 100 1/31/2017 Rio de Janeiro 100 1/31/2017 Rio de Janeiro 100 1/31/2017 Brasilia 100 1/31/2017 Brasilia 100 2/15/2017 Rio de Janeiro 150 2/15/2017 Rio de Janeiro 150 2/15/2017 Brasilia 150 2/15/2017 Brasilia 150 2/28/2017 Rio de Janeiro 200 2/28/2017 Rio de Janeiro 200 2/28/2017 Brasilia 200 2/28/2017 Brasilia 200 12/31/2016 Sao Polo 25 12/31/2016 Sao Polo -25 12/31/2016 Rio de Janeiro 25 12/31/2016 Rio de Janeiro 25 12/31/2016 Brasilia 25 12/31/2016 Brasilia 25 2/28/2017 Sao Polo 150 2/28/2017 Sao Polo -150 2/15/2017 Sao Polo 200 2/15/2017 Sao Polo -200 12/15/2016 Sao Polo 12.5 12/15/2016 Sao Polo -12.5 12/15/2016 Rio de Janeiro 12.5 12/15/2016 Rio de Janeiro 12.5 12/15/2016 Brasilia 12.5 12/15/2016 Brasilia 12.5

Then I create these 2 COLUMNS plus a simple Sum MEASURE for the Hours

1) Include Cities COLUMN

```Include Cities =
IF (
'Table'[Fortnight] = MAX ( 'Table'[Fortnight] )
&& CALCULATE (
SUM ( 'Table'[Hours] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[City] ),
'Table'[Fortnight] = MAXX ( 'Table', 'Table'[Fortnight] )
)
)
> 0,
'Table'[City],
BLANK ()
)```

2) Visual Filter COLUMN (to be used in the Visual Level Filter)

```Visual Filter Column =
CONTAINS (
VALUES ( 'Table'[Include Cities] ),
'Table'[Include Cities], 'Table'[City]
)```

3) Total Hours MEASURE

`Total Hours = SUM ('Table'[Hours] )`

And here's the result

The Total Hours > 0 only works when the City had 0 Hours for all Fortnights showing in the Matrix (basically the entire row)

If even one Fortnight has > 0 you will see the City in the Matrix

Hope this helps!

All Replies
Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Show all values ​​if in the last fortnight is different from 0

Visual Level Filters - open the Hours field - Show Items when the value is: - select is greater than - type 0 - click Apply Filter

Regular Visitor
Posts: 19
Registered: ‎09-19-2016

## Re: Show all values ​​if in the last fortnight is different from 0

Hi Sean,

I had tried this solution. I need to show "0" in the previous fortnights, not in the last fortnight.

Anyway, did not work, note that in the picture above the cities (Brasilia, Porto Alegre) that had value "0" in every fortnight were removed, and the one that had a value greater than 0 in at least a fortnight was displayed (picture below).

Renato França

Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Show all values ​​if in the last fortnight is different from 0

[ Edited ]

Can you post the Measure formula? And do you have other fields in the Matrix?

Sample data we can test on would help too

EDIT: @renatofrancavr

Can you check the Field you have in the Rows - Uncheck Show Items with No Data

Regular Visitor
Posts: 19
Registered: ‎09-19-2016

## Re: Show all values ​​if in the last fortnight is different from 0

Hi Sean,

Follows the picture below with the fields, filters in power bi and the base in excel.

Regards,

Renato

Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Show all values ​​if in the last fortnight is different from 0

Try couple things...

1) Create a MEASURE

```Hours 2 MEASURE =
SUM ( 'Productivity Report BRDC'[Susp & Unassign Hours] )```

And use it in the Matrix instead of the column - Test it first without any IF statement.

2) Then UNCHECK Show items with no data for the Dept City in the Rows of the Matrix

Let me know!

Regular Visitor
Posts: 19
Registered: ‎09-19-2016

## Re: Show all values ​​if in the last fortnight is different from 0

Hi Sean,

I followed the steps above. The result is still not correct.

1) In the table "With the fltro> 0" showed 1 city that has "0" in the last fortnight.

2) In the table "No filter" : showed 3 cities that have "0" in the last fortnight.

Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Show all values ​​if in the last fortnight is different from 0

Can you post some sample data of all columns used to create this Matrix Visual?

Regular Visitor
Posts: 19
Registered: ‎09-19-2016

## Re: Show all values ​​if in the last fortnight is different from 0

Hi Sean,

View Table:

Report View:

Let me know if this helps.

Regards,

Renato França

Highlighted
Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Show all values ​​if in the last fortnight is different from 0

[ Edited ]

@renatofrancavr  Okay I think we got this now!

You want to Show ONLY the Cities that have Hours in the Last Fortnight! So even if they had Hours in the previous fortnights showing in the Matrix  do not show them unless they have Hours in the last Fortnight!

So first I created this sample data in 'Table' with 3 Columns

Fortnight City Hours

 1/15/2017 Sao Polo 50 1/15/2017 Sao Polo 50 1/15/2017 Rio de Janeiro 50 1/15/2017 Rio de Janeiro 50 1/15/2017 Brasilia 50 1/15/2017 Brasilia 50 1/31/2017 Sao Polo 100 1/31/2017 Sao Polo 100 1/31/2017 Rio de Janeiro 100 1/31/2017 Rio de Janeiro 100 1/31/2017 Brasilia 100 1/31/2017 Brasilia 100 2/15/2017 Rio de Janeiro 150 2/15/2017 Rio de Janeiro 150 2/15/2017 Brasilia 150 2/15/2017 Brasilia 150 2/28/2017 Rio de Janeiro 200 2/28/2017 Rio de Janeiro 200 2/28/2017 Brasilia 200 2/28/2017 Brasilia 200 12/31/2016 Sao Polo 25 12/31/2016 Sao Polo -25 12/31/2016 Rio de Janeiro 25 12/31/2016 Rio de Janeiro 25 12/31/2016 Brasilia 25 12/31/2016 Brasilia 25 2/28/2017 Sao Polo 150 2/28/2017 Sao Polo -150 2/15/2017 Sao Polo 200 2/15/2017 Sao Polo -200 12/15/2016 Sao Polo 12.5 12/15/2016 Sao Polo -12.5 12/15/2016 Rio de Janeiro 12.5 12/15/2016 Rio de Janeiro 12.5 12/15/2016 Brasilia 12.5 12/15/2016 Brasilia 12.5

Then I create these 2 COLUMNS plus a simple Sum MEASURE for the Hours

1) Include Cities COLUMN

```Include Cities =
IF (
'Table'[Fortnight] = MAX ( 'Table'[Fortnight] )
&& CALCULATE (
SUM ( 'Table'[Hours] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[City] ),
'Table'[Fortnight] = MAXX ( 'Table', 'Table'[Fortnight] )
)
)
> 0,
'Table'[City],
BLANK ()
)```

2) Visual Filter COLUMN (to be used in the Visual Level Filter)

```Visual Filter Column =
CONTAINS (
VALUES ( 'Table'[Include Cities] ),
'Table'[Include Cities], 'Table'[City]
)```

3) Total Hours MEASURE

`Total Hours = SUM ('Table'[Hours] )`

And here's the result

The Total Hours > 0 only works when the City had 0 Hours for all Fortnights showing in the Matrix (basically the entire row)

If even one Fortnight has > 0 you will see the City in the Matrix

Hope this helps!