Forum Discussion
Can rows be hidden from a Table visual without removing the rows from a measure shown in the Table?
Hello,
Is there a way to filter/hide rows in a Power BI table visual and keep the associated measure counts for those rows?
My table below summarizes a 36 month lookback for IDs which have a lifecycle. The 'Active IDs During Reporting Month' column is a measure that counts IDs by 'Reporting Month' if "Active", including previous months.
| Reporting Month | Count of ID | Active ID Count |
Active IDs During Reporting Month | Show Row |
| 1/1/2026 | 4 | 4 | 60 | 1 |
| 12/1/2025 | 9 | 9 | 59 | 1 |
| 11/1/2025 | 10 | 10 | 52 | 1 |
| 10/1/2025 | 7 | 7 | 45 | 1 |
| 9/1/2025 | 4 | 4 | 42 | 1 |
| 8/1/2025 | 8 | 8 | 42 | 1 |
| 7/1/2025 | 7 | 7 | 38 | 1 |
| 6/1/2025 | 1 | 1 | 36 | 1 |
| 5/1/2025 | 3 | 3 | 38 | 1 |
| 4/1/2025 | 2 | 2 | 37 | 1 |
| 3/1/2025 | 5 | 5 | 36 | 1 |
| 2/1/2025 | 4 | 4 | 34 | 1 |
| 1/1/2025 | 6 | 6 | 33 | 1 |
| 12/1/2024 | 6 | 4 | 27 | 1 |
| 11/1/2024 | 3 | 3 | 26 | 1 |
| 10/1/2024 | 4 | 4 | 25 | 1 |
| 9/1/2024 | 3 | 3 | 24 | 1 |
| 8/1/2024 | 4 | 4 | 25 | 1 |
| 7/1/2024 | 3 | 3 | 21 | 1 |
| 6/1/2024 | 1 | 1 | 21 | 1 |
| 5/1/2024 | 2 | 2 | 21 | 1 |
| 4/1/2024 | 3 | 3 | 22 | 1 |
| 3/1/2024 | 4 | 4 | 25 | 1 |
| 2/1/2024 | 6 | 6 | 24 | 1 |
| 1/1/2024 | 6 | 5 | 26 | 0 |
| 12/1/2023 | 22 | 0 | ||
| 11/1/2023 | 2 | 2 | 28 | 0 |
| 10/1/2023 | 1 | 1 | 28 | 0 |
| 9/1/2023 | 5 | 5 | 31 | 0 |
| 8/1/2023 | 3 | 3 | 26 | 0 |
| 7/1/2023 | 6 | 6 | 24 | 0 |
| 6/1/2023 | 3 | 3 | 18 | 0 |
| 5/1/2023 | 5 | 5 | 16 | 0 |
| 4/1/2023 | 5 | 5 | 11 | 0 |
| 3/1/2023 | 6 | 5 | 6 | 0 |
| 2/1/2023 | 1 | 1 | 1 | 0 |
My table visual needs to display 'Reporting Month' within the last 24 months (not including current month).
I created a flag for 'Reporting Month' more than 24 months from current = 'Include Row' but when I use it as a filter, the 'Active IDs...' count filters out the 'Reporting Month' where 'Include Row' = 0. I also tried using 'Reporting Month' as a relative date filter (within the last 24 month), but that also removes the 'Active IDs...' count for the months filtered out (2/1/23-1/1/24)
What I am trying to achieve is for my table visual to display a 'Reporting Month' range = 1/1/26 - 2/1/24, where 'Active IDs...' = 25 for 'Reporting Month' = 3/1/24 and so on, as shown in the table above, while filtering/hiding the rows where 'Show Row' = 0 (2/1/23 - 1/1/24)
Thank you in advance for any help or suggestions you may have!
I have found the solution is to create a visibility flag measure to identify the rows that are within 24 months of the current date, as below, and then use that measure as a filter on the visual. For some reason the visibility flag measure does not working for 'Filters on this page' but does work for 'Filters on this visual'.
Within24Months (measure) =VAR CurrentDate = TODAY()VAR RowDate = MAX('Date'[Date])VAR MonthDifference = DATEDIFF(RowDate, CurrentDate, MONTH)RETURNIF(MonthDifference >= 0 && MonthDifference <= 24, 1, 0)
8 Replies
Hi,
Shouldn't the number against 6/1/2023 under the "Active IDs During Reporting Month" column be 19 i.e. 16+3? How did you calculate 18 there? Also, the 24th month counted backward from 1/1/26 should be 2/1/24? Therefore, shouldn't the answer be 24? Lastly, is the input data the first 2 columns?
Hi Ashish_Mathur,
For context, my 'Active IDs..' measure is dynamic to account for each ID having a "lifecycle".
With respect to 6/1/23, 18 is the correct number as one ID has a "lifecycle" that began and ended 3/1/23 and another ID that began 4/1/23 and ended 5/1/23. Does that make sense?
Columns 2,3,5 are for validation and will be removed from final dashboard.
Thank you very much for your time reviewing and replying!You are welcome. I am still confused. I do not know the result that you are expecting.
You can conditionally forma Show row to be transparent and replace the column name with a blank space.
Use this measure as a field value in conditional formatting dialogue
Transparent = "RGBA (0, 0, 0, 0 )"It just makes it invisible in the visual but if the data is exported, it will be included.
I have found the solution is to create a visibility flag measure to identify the rows that are within 24 months of the current date, as below, and then use that measure as a filter on the visual. For some reason the visibility flag measure does not working for 'Filters on this page' but does work for 'Filters on this visual'.
Within24Months (measure) =VAR CurrentDate = TODAY()VAR RowDate = MAX('Date'[Date])VAR MonthDifference = DATEDIFF(RowDate, CurrentDate, MONTH)RETURNIF(MonthDifference >= 0 && MonthDifference <= 24, 1, 0)
