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

[renatofrancavr]

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

[Sean]

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

[renatofrancavr]

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).

Thanks for your help.

Renato França

[Sean]

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

[renatofrancavr]

Hi Sean,

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

Regards,

Renato

[Sean]

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!

[renatofrancavr]

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.

Follow picture below.

[Sean]

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

[renatofrancavr]

Hi Sean,

Follow the picture below:

View Table:

Report View:

Let me know if this helps.

Regards,

Renato França

[Sean · Accepted solution]

@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!

[renatofrancavr]

Hi Sean,

With the formulas above, we got it! \o/

Thank you very much!!! 

Just one more point, if I want to add another column in "Row" in my base, where do I change in the formula to consider this column, for example: "Level"?

Best Regards,

Renato