Reply
Regular Visitor
Posts: 19
Registered: ‎09-19-2016
Accepted Solution

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

 

img1.png

Can you help me?

 

Regards,

Renato França


Accepted Solutions
Highlighted
Super Contributor
Posts: 1,991
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! Smiley Happy

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/2017Sao Polo

50

1/15/2017Sao Polo50
1/15/2017Rio de Janeiro50
1/15/2017Rio de Janeiro50
1/15/2017Brasilia50
1/15/2017Brasilia50
1/31/2017Sao Polo100
1/31/2017Sao Polo100
1/31/2017Rio de Janeiro100
1/31/2017Rio de Janeiro100
1/31/2017Brasilia100
1/31/2017Brasilia100
2/15/2017Rio de Janeiro150
2/15/2017Rio de Janeiro150
2/15/2017Brasilia150
2/15/2017Brasilia150
2/28/2017Rio de Janeiro200
2/28/2017Rio de Janeiro200
2/28/2017Brasilia200
2/28/2017Brasilia200
12/31/2016Sao Polo25
12/31/2016Sao Polo-25
12/31/2016Rio de Janeiro25
12/31/2016Rio de Janeiro25
12/31/2016Brasilia25
12/31/2016Brasilia25
2/28/2017Sao Polo150
2/28/2017Sao Polo-150
2/15/2017Sao Polo200
2/15/2017Sao Polo-200
12/15/2016Sao Polo12.5
12/15/2016Sao Polo-12.5
12/15/2016Rio de Janeiro12.5
12/15/2016Rio de Janeiro12.5
12/15/2016Brasilia12.5
12/15/2016Brasilia12.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

Matrix Hide Rows with No Values in PP.gif

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! Smiley Happy

View solution in original post


All Replies
Super Contributor
Posts: 1,991
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).

 

img2.png

 

Thanks for your help.

Renato França

Super Contributor
Posts: 1,991
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 Smiley Happy

 

Show Items with No Data - Matrix Rows.png

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.

 

img1.png

 

Regards,

Renato

Super Contributor
Posts: 1,991
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

Show Items with No Data - Matrix Rows2.png

Let me know! Smiley Happy

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. Smiley Sad

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.

 

 

img1.png

Super Contributor
Posts: 1,991
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,

 

Follow the picture below:

 

View Table:

 

image1.JPG

 

 

Report View:

 

image2.JPG

Let me know if this helps.

 

Regards,

Renato França

 

 

Highlighted
Super Contributor
Posts: 1,991
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! Smiley Happy

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/2017Sao Polo

50

1/15/2017Sao Polo50
1/15/2017Rio de Janeiro50
1/15/2017Rio de Janeiro50
1/15/2017Brasilia50
1/15/2017Brasilia50
1/31/2017Sao Polo100
1/31/2017Sao Polo100
1/31/2017Rio de Janeiro100
1/31/2017Rio de Janeiro100
1/31/2017Brasilia100
1/31/2017Brasilia100
2/15/2017Rio de Janeiro150
2/15/2017Rio de Janeiro150
2/15/2017Brasilia150
2/15/2017Brasilia150
2/28/2017Rio de Janeiro200
2/28/2017Rio de Janeiro200
2/28/2017Brasilia200
2/28/2017Brasilia200
12/31/2016Sao Polo25
12/31/2016Sao Polo-25
12/31/2016Rio de Janeiro25
12/31/2016Rio de Janeiro25
12/31/2016Brasilia25
12/31/2016Brasilia25
2/28/2017Sao Polo150
2/28/2017Sao Polo-150
2/15/2017Sao Polo200
2/15/2017Sao Polo-200
12/15/2016Sao Polo12.5
12/15/2016Sao Polo-12.5
12/15/2016Rio de Janeiro12.5
12/15/2016Rio de Janeiro12.5
12/15/2016Brasilia12.5
12/15/2016Brasilia12.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

Matrix Hide Rows with No Values in PP.gif

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! Smiley Happy