Forum Discussion

E_Rye's avatar
E_Rye
Frequent Visitor
7 months ago
Solved

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 li...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • E_Rye's avatar
    E_Rye
    7 months ago

    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)
    RETURN
        IF(MonthDifference >= 0 && MonthDifference <= 24, 1, 0)

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 months ago

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?

E_Rye's avatar
E_Rye
Frequent Visitor
7 months ago

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!
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 months ago

    You are welcome.  I am still confused.  I do not know the result that you are expecting.

    • E_Rye's avatar
      E_Rye
      Frequent Visitor
      7 months ago

      Final result is to display 'Reporting Month' range = 1/1/26 - 2/1/24 and filter/hide 'Reporting Month' more than 24 months from current month (2/1/23-1/1/24) while keeping the corresponding 'Active IDs During Reporting Month' shown in my table visual.

      I have tried filtering several different ways (see original post) but not getting the needed result.

       

      Thanks again!