Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
Visual Level Filters - open the Hours field - Show Items when the value is: - select is greater than - type 0 - click Apply Filter
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
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
Hi Sean,
Follows the picture below with the fields, filters in power bi and the base in excel.
Regards,
Renato
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!
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.
Can you post some sample data of all columns used to create this Matrix Visual?
Hi Sean,
Follow the picture below:
View Table:
Report View:
Let me know if this helps.
Regards,
Renato França
@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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |