Forum Discussion
SELECT MAX(Date) FROM Source WHERE {Reference_Column] IS NOT NULL
How can I accomplish the above T-SQL statement in Power BI?
I have a table with 4 Columns (Date, Name, Age, Gender) to keep track of who led which training session by date (thats obviously not the real table but it makes this example nice and simple :smileyhappy:).
I need to select the date value in the row which has the most recent date AND has a value in the Name column (the rows follow the financial year and so reach into next year with null values in Name for all rows with future dates)
Please ask any questions if I have been unclear and thanks in advance
For anyone with the same issue.
I ended up putting an IF statement into a custom column on the table which checks to see if my reference column has a value, if so it is populated with the corresponding date in that row, if not it is populated with null. After that I used a MAX() function in a measure to get the latest available date from my new column and used that measure in a visual level filter.
It feels like a long way to go for SELECT ColA FROM Table1 WHERE ColB IS NOT NULL but it had the desired effect. If anyone knows a better solution or can give me some insight I am missing I would be grateful.
Thanks
2 Replies
For anyone with the same issue.
I ended up putting an IF statement into a custom column on the table which checks to see if my reference column has a value, if so it is populated with the corresponding date in that row, if not it is populated with null. After that I used a MAX() function in a measure to get the latest available date from my new column and used that measure in a visual level filter.
It feels like a long way to go for SELECT ColA FROM Table1 WHERE ColB IS NOT NULL but it had the desired effect. If anyone knows a better solution or can give me some insight I am missing I would be grateful.
Thanks