Forum Discussion
Filter a table visual by latest date
I have a question very similar to this one: https://community.fabric.microsoft.com/t5/Desktop/Power-BI-table-visual-to-get-only-latest-date-value/m-p/223073
I have a table visual that looks like this:
covered the numbers for PHI, but in the "Protocol No" column its the name of a study, lets say like "LS-P-Tylenol" and in the "Sequence Number" column its a number that represents a patient. Lets say "105".
Well the problem is right now this table will have like 50 rows for a single patient. It'll say protocol no: "LS-P-Advil" and sequence number: "402" over and over and over, one row for every visit date they have planned..
So I'd love to filter this table by the soonest (from today) "planned visit date" per patient.
Problem is: this visual comes from three separate queries (otherwise I'd filter in the query):
based on that other answer I've tried a few things. First thing I tried was summarizing planned visit date by latest (or earliest):
I'm not really sure what that does but I'll still have multiple rows per patient.
I also tried a filter on the visual like this:
It definitely filtered the results, but not sure how? I'm left with only like 30 rows (should be hundreds) and for the people left, its not even their most recent visit date.
Any ideas?
Hello, I've tried to replicate the datasets, you can use a measure to calulate the latest date for the ID. Navigate to modelling tab, and then New Measure. In the formula bar enter this formula
Latest Visit = CALCULATE(MAX('Sheet1'[Date]), ALLEXCEPT('Sheet1', 'Sheet1'[Seq])). Change the Sheet1 name with your table name. Then you can bring this measure to your table/matrixI hope it helps!
3 Replies
Hi JoeCrozier. Are you looking to filter by date let's say after May 31, 2023, or you are looking to sort the results by earliest date. If sorting helps you, go to the the Data View, navigate to the table, and in the Column Tools, select sort by column "Ascending or Descending". Hope it hepls
AbhinavJoshi thank you for replying!
So our intent with this page is really just to check that for each subject, they do or do not have an upcoming planned visit date. So as you can see in the above photo, that subject outlined in the middle is shown in three rows, his/her next 3 visits. Well, I dont need all three of his visits, a row for each. I just need ONE row with their next visit. So i'd love to filter to one row for that '158' patient. Does my phrasing make sense?
Hello, I've tried to replicate the datasets, you can use a measure to calulate the latest date for the ID. Navigate to modelling tab, and then New Measure. In the formula bar enter this formula
Latest Visit = CALCULATE(MAX('Sheet1'[Date]), ALLEXCEPT('Sheet1', 'Sheet1'[Seq])). Change the Sheet1 name with your table name. Then you can bring this measure to your table/matrixI hope it helps!