How to create a filter that controls the DirectQuery directly?

[Anonymous]

I have a failry large dataset that I am using DirectQuery for. I want to summarize the data to just the results so that the DirectQuery works efficiently.

Example: 

let startTime = datetime(2024-01-01);

let endTime = now();

TableA

| where Timestamp between (startTime .. endTime)

| summarize dcount(ColumnA)

I want the query to run based on my PowerBi filter so that the start time and end time can be directly controlled by the slicers put in place in PowerBi. 

[Anonymous]

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @Anonymous , based in your description, you can use the dynamic paramater in power query, you can refer to the following link about it.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

That's how Direct Query works anyway.  What have you tried and where are you stuck?

[Anonymous]

Hi! I am currently stuck on two aspects. I am currently trying to create a relative date visualization slicer for a time filter I am trying to make. I created Start Date and End Data parameters to be able to manipulate the data from the DirectQuery directly on Power Bi. 

This is how it currently looks but I would like for it to look like this:

My other question is how to join on two direct queries. I want to be able to filter based on a column called Organization which is available on both my columns. Table A and B are joined Many to Many. This slicer is based on the Organization of Table A so when I try to filter the data, my information from Table B breaks and this pops up:

However if i remove that filter, it shows up just fine.

Any suggestions?

[lbendlin]

You can either hand over a date range to the parameter (and handle that in your Power Query code) or use separate tables for the Start and End date ranges.  I would suggest the first option.

Here is one possible implementation Chris Webb's BI Blog: Handling Multi-select In Power BI Dynamic M Parameters (crossjoin.co.uk)

[Anonymous]

I already have separate tables for the Start and End date ranges as it looks in the first picture of my last reply. My question is that if I hand over a date range to the parameter, will the Start and End dates be selected as two individual dates or will it take all the dates between the two. 

Example:

Selecting Start date= 1/1/2024

Selecting End Date = 6/1/2024

will it only show me dates from 1/1/2024 and 6/1/2024 or will it give me all the data points from 1/1/2024-6/1/2024. I would need it to get all data points from 1/1/2024-6/1/2024.