Apply filter to a table and Join to another table without using merge query

[BenazirMohammad]

Hi,

Here is what I am trying to do.

I have 2 tables.

1) Calendar Table (Year)

Year

2016

2017

2018

2019

2020

2) Sales Table (Year, Employee, Value)

Employee , Year,Value

a, 2020,1

b,2020,1

c,2020,1

d,2019,1

e,2018,1

a,2018,1

The 2 tables are joined using [Year]

I would like to filter Year in Calendar table which I am able to do using filter in Power Query. The filter I have is Year >=2019

But its showing

Employee , Year,Value

a, (blank),1

a,2020,1

b,2020,1

c,2020,1

d,2019,1

e,(Blank),1

My expected result was  4 rows

Employee , Year,Value

a,2020,1

b,2020,1

c,2020,1

d,2019,1

Is it something I could achieve without using merge queries. My dataset is quite large, using Direct Query and SnowFlake.

Thanks

[v-juanli-msft]

Hi @BenazirMohammad 

Please delete the "filter rows" step in your edit queries,

If your "date" table has all dates which are in "sales" table,

you can use "CAL_DATE' in a slicer on reports, then it would filter your table visual.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BenazirMohammad]

Thanks. I can certainly use slicer, but I was hoping if there was another way using filter on the dataset. Not sure why it takes it as a left join and shows all the rows from sales table. Is there another way?

[edhans]

---

@BenazirMohammad wrote:

Thanks. I can certainly use slicer, but I was hoping if there was another way using filter on the dataset. Not sure why it takes it as a left join and shows all the rows from sales table. Is there another way?

---

I think this is the issue @BenazirMohammad. You are thinking like a SQL person here. This isn't a left join. Relationships in DAX are not joins, they are filter relationships. They aren't inner, outer, left, or right. From a filtering standpoint, they appear to operate as a left join would, but it is a filter between tables, and you can have stuff in your FACT tables not in your DIM tables. I use Power Query to clean this up. That is what PQ is for, modeling. Don't try to model in DAX. Don't try to analyze in Power Query. Use the right tool for the job. Power Query will let you merge with a left/inner/outer/right/anti and even crossjoin. And those are honest to goodness joins. Not filters. 

This is also why you cannot have more than 1 active relationship between tables. You can have 10 relationships but only 1 is active and the other 9 are inactive, and you can activate with the USERELATIONSHIP() modifier. Because you cannot have multiple filters operating simultaneously, which is very different from a join that can be based on 10 fields at once.

Make sense?

[BenazirMohammad]

makes sense.. thanks for your time and explanation.

How would you go about doing this in pwer query.

My dataset is quite large - using direct query / snowflake.

I have tried merge query and it takes a loong time. 

[edhans]

If in Direct Query, the merge is folded back to the server. If it takes a long time, then that is really a server issue, and I'm not sure I have a quick fix for you. What exactly are you trying to filter? I may have lost the plot. 

What if you created a list of the items you wanted from table A to appear in table B, then use List.Contains()? See this example. That will fold in SQL server and is stupid fast. I do not know if it will fold for SnowFlake though. Won't know until you try it.

[BenazirMohammad]

I think I oversimplified the scenario a bit.

Here is how the actual table structure. The data is joined by InvoiceDate(Sales) to Date(Sales)

The filter is on Year in Sales table.

The calendar table is a table containing list of dates and various dates component.

1) Calendar Table (Year,Date)

Year, Date , Month, Quarter....

2016, 01/01/2016,1,1

2016, 01/02/2016,1,1

2016, 01/03/2016,1,1

...

2017,01/01/2017,1,1

...

2018, 01/01/2018,1,1

..

2019, 01/01/2019,1,1

..

2020, 01/01/2020,1,1

...

2020, 01/01/2020,1,1

2) Sales Table (Employee,InvoiceDate, Value) 

Employee , InvoiceDate,Value

a, 01/01/2020,1

b,01/01/2020,1

c,01/01/2020,1

d,01/06/2019,1

e,01/10/2018,1

a,06/15/2018,1

Final output (Expected)

Employee , InvoiceDate,Value, Year(from Calendar), Month(From calendar), quarter(from calendar)

a, 01/01/2020,1,2020,1,1

b,01/01/2020,1,2020,1,1

c,01/01/2020,1,2020,1,1

d,,01/06/2019,1,2019,6,2

[edhans]

Change the filter to be the year on the Calendar table, no the sales table. You filter your DIM tables, not FACT tables as a rule. DIM tables filter FACT tables. FACT tables cannot filter DIM tables unless bi-directional filtering is on, which is rarely a good idea. 

If you need more help, see links below for providing data and ideally your PBIX file.

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

[BenazirMohammad]

sorry typo Year is in calndar table and its filtered on Calendar table.

Will post the .pbix 

[BenazirMohammad]

here is the .pbix

https://drive.google.com/file/d/1N0N24bxYHUU_qVsAGR414tzDlW-uuPYC/view?usp=sharing

thanks for your help

[Ashish_Mathur]

Hi,

Why do you need to use Power Query to filter?  To your visual, drag Year from the Calendar Table and the other columns from the second table.  Now filter the table with the Calendar Year >=2019

[edhans]

Power Query is where you should filter the data if you don't want it in the model at all. No point bringing in a bunch of data you don't need and have to filter it in the model somehow. 

That what power query is for. Modeling and shaping data. 😊

but you just need to apply those filters to all of the relevant tables. Not just one of them. I do this all of the time. Makes life in DAX so much easier, and can make the model faster and smaller too. 

[edhans]

Why not just filter your data table by the same => 2019?

If there is some reason that won't work, you can do this with List.Contains, but it will only perform well on such a large dataset if your data source supports folding for this. In SQL Server, Power Query will use the In operator when you do this. No merging necessary. I wrote an article on this just today by coincidence.

Basically, you'd filter your main table like this:

Create a list using a blank query that would be =List.Distinct(Calendar[Year]) and call it UsedYears

Then in your data table, you need to remove any years not in your calendar table.

=Table.SelectRows(#"Previous step", each List.Contains(UsedDates, [Year]))

If it folds, awesome. If not, you'll have to use a merge if the simple filter at the top of this post won't work.

[d_gosbell]

You will need to apply your filter to the Sales table as well. The blanks are for where you have sales data that does not match a row in your calendar table. (this should have the added benefit of pushing this filter down to snowflake and allowing it to be evaluated server side)

[BenazirMohammad]

Thanks. But I am not sure where to apply filter on Sales table ?

[edhans]

Hi @BenazirMohammad - I'm a little confused. In your original post you said "The filter I have is Year >=2019" in your calendar table in Power Query.

Don't you have a date in your sales table? Just filter on the date there as well in Power Query to be => #date(2019,1,1). All you have to do is select the dropdown and type i a date. Power Query will put i the #date syntax for you.

If that isn't clear, or we've misunderstood your original post, please post back with a screenshot of how your original filter is applied to give us some context.