Filter Expression By Another Column

[Anonymous]

So I have a tabular model table expression, and I'm trying to filter rows based on a particular column in another expression/table.

I have a Date table that has rows for each date going back to 1980. I inherited this table so...go easy! I would like the expression to filter on a second table - DimSchedule. Ideally, I only want to populate the Date expression with dates that are inbetween the maximum and the minimum of the ActualScheduleDate column of the DimSchedule table.

Here is the current expression:

= Source{[Schema="common", Item="V_DimDate"]}[Data]

Thanks for any help!!

[Anonymous · Accepted solution]

I managed to figure it out:

let
Source = Value.NativeQuery(#"SQL/azsds001d database windows net;EDW", "SELECT * FROM common.V_DimDate
WHERE [Date] <= (SELECT MAX(ScheduleDepartureDateZ)
FROM ops.DimFlightSchedule)
AND
[Date] >= (SELECT MIN(ScheduleDepartureDateZ)
FROM ops.DimFlightSchedule)")
in
Source

[edhans]

Hi @Anonymous - I am not 100% sure I am clear on what you want. Perhaps List.Contains() will work. It will filter one column on another within a Table.SelectRows() function. You can read my blog post about using it for this purpose here.

If that is not what you need, please be clearer with some sample data and expected output.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Thanks for you reply. I'm not actually trying to filter a column on another column. Essentially, I have an expression in a tabualr model that is being used as a date table. This expression as it exists today returns all rows from a view called v_DimDate. This result set is used a  tabualr model in SSAS. I don't want every row from this view. I only want rows where the Data column falls between the min and max of a differnt column from a different table.

Essentialy, this is the query if it were written in SQL

SELECT * FROM v_DimDate WHERE [Date] <= (SELECT MAX(ScheduleDate) FROM DimFlightSchedule) AND [Date] >= (SELECT MIN(ScheduleDate) FROM DimFlightSchedule)

I know SQL is nothing like DAX, but I'm fairly new to it, so still learning.