Restricting a direct query to a date range resulting from a selected record of an unrelated table

[HyperT]

I have spent almost 2 days on figuring out how to do this, but every road leads me to a dead end...

Consider to have a table of production campaigns with a start and an end date of the campaign as columns in that table.

Futhermore there are tabes with measurements made over time, not directly related to the campaigns, but having timestamps telling when the measurements were made. These tables are so massive, that querying them is only feasible in direct query mode (Azure Databricks).

The simple thing I want is, to select a record from the campaign table and get (e.g. as a drillthrough) all records from the measurement table, where the timestamp is between the start-date and the end-date of the campaign.

What almost brought me there was to define 3 measures and use them to identify the relevant target records :

• MinDateTime = MINX( AllSelected( campaigns, campaigns[start-date] ) )
• MaxDateTime = MAXX( AllSelected( campaigns, campaigns[end-date] ) )
• DateInRange = IF( calculate( MINX( measurements, measurements[timestamp] ) ) >= campaigns[MinDateTime]  && calculate( MAXX( measurements, measurements[timestamp] ) ) <= campaigns[MaxDateTime] , 1, 0 )

When displaying the DateInRange as a visual column in the measurements-table, it shows correct values next to each line, meaning 1, whenever the records are in the date raneg and otherwise 0. Yet, when setting a visual filter on it to only display the records with DateInRange = 1, instead of all the records with a vaule of 1 it just shows a single record (with no clue, how that single one was chosen).

DateInRange not filteredDateInRange filtered on 1

This is the closest I got. All other approaches I tried, didn't even get close.

In the end I just want to retrieve a parameter for a direct query from a data selection...

Anyone an idea on that?

[krishnakanth240]

Hi @HyperT 

Issue is that you are using a measure as a row level filter which does not work in DirectQuery because measures are evaluated in filter context and not per row. Due to which filtering on DateInRange gives inconsistent results. 

Instead, you can try applying date range logic inside CALCULATE or via proper filter context like drillthrough or relationships. So the filter is pushed correctly to the data source and behaves as expected.

[HypertT]

Thanks rishnakanth240

you are pointing to the right prerequisite with the Drillthroughs. Separating the synced and the direct queries on separate pages and using drillthroughs when switching to the directs is the first step of making it work, but that's what I already had. The dynamic parameters are the bit you need on top to get the direct queries set up int the good manner...

[grazitti_sapna]

Hi @HyperT ,

Issue in your DAX is with MAXX and MINX functions.

Replace MAXX with MAX and MINX with MIN. Below is the DAX, also attached a sample .pbix file for you.

DateInRange = 
VAR MinDate =
SELECTEDVALUE(Campaigns[StartDate])

VAR MaxDate =
SELECTEDVALUE(Campaigns[EndDate])

RETURN
IF(
    MAX(Measurements[Timestamp]) >= MinDate &&
    MAX(Measurements[Timestamp]) <= MaxDate,
    1,
    0
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[HypertT]

Hello grazitti sapna,

thanks for your feedback, but actually that is what I had tried before and which would not do the job at all. It was only by using the X-variants that I got working queries at all, but the overall approach seems to have not been the good one, when mixing synced and direct queries.

[Deku · Accepted solution]

This sounds like it be a job for Dynamic M parameters

https://www.google.com/amp/s/blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-paramete...

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

[HypertT]

Thanks Deku,

indeed that is what I was out for. Wasn't aware about the possibility to tie M-Query Parameters to Table columns in PBI (it's pretty well hidden... ;o)). This, combined with the Drillthroughs I was already deploying for the Direct Query content, made it work. Now, when I select a campaign, my Drillthrough buttons light up and when navigating to a page the parameters are taken into account, because all Direct Querys on them have the parameters deployed.

Thanks a lot.

[Deku]

Happy to help