Please help with Dataflow(s)

[jcampbell474]

I have a dataflow to use in multiple datasets.  I can't figure out how to filter the dataflow load into each dataset.  Each dataset will report different date ranges and products.

I don't need to filter the dataflow - it should contain all records (and be incrementally refreshed).  I need to use it as source data for a few other datasets/pbix's.  

I know that I can load the entire dataflow and filter to what's needed in PowerQuery, but really do not want to do that.  It's a large amount of data.

Is there a way to filter a dataflow load into a pbix?  

[BA_Pete]

Hi @jcampbell474 ,

You can't do this using a dataflow. You can do this via Query Folding when working with SQL servers, but a dataflow is essentially just a Power Query transformation in itself, so the opportunity for query folding has already passed by the time it comes into your model.

Obviously I don't know what kind of data volume you're working with, or how many adjustments you're looking to make, but an individual dataflow filtered to your requirements for each report may be your best bet here.

If you're on Premium Capacity, you can reference your main dataflow and filter the reference result to your requirements. This should reduce some of the processing load in producing multiple outputs.

Pete

[jcampbell474]

Thank you.  Yes, we're on premium capacity.

To make sure I understand, what do you mean by "filter the reference result to your requirements"?  Simply filter the dataflow itself to what's needed in the dataset?

Edit: Are you referring to having a 'base' dataflow, then create filtered dataflows sourced from it?

[BA_Pete · Accepted solution]

Yes, I think that would be the most efficient setup i.e. one 'base' data flow that brings in everything, then a number of other tables that reference (not duplicate) the base dataflow, but are filtered as required for the other different reports.

If your source that feeds the dataflow is an SQL server, I think the most efficient in terms of load may be connecting directly to the source from each report, as you could then make full use of query folding and staggered refresh times, but YMMV.

Pete