Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How to create a filter that controls the DirectQuery directly?

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. 
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    lbendlin
    Super User
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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?