Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
H3nning
Resolver I
Resolver I

Find selected value in many to many relation

Hi,

 

the following problem is part of a bigger, much more complex problem, so please dont ask for the sense in the model. It has to be that way because of the bigger context which would take much more text to explain.

 

Situation as follows: 

i have a table Facts

IDValueFromtoConnect
A101.01.202310.01.20231
A210.01.202331.01.20231
B101.01.202310.01.20231
B210.01.202320.01.20231
B320.01.202331.01.20231

 

And I have a timetable like this:

DateConnect
01.01.20231
02.01.20231
......
30.01.20231
31.01.20231

 Both tables are connected over the two connect columns, so every row is connected to every row.

 

There is a slicer with Date in it and single select.

 

What i want to achieve is a measure, that takes into account which date ist selected in the slicer and then filters a visual depending wheter the row is actual (means >= From and < To) or not.

 

So for example if i select the 21.01.2023 then the resulting facts should look like this:

 

IDValueFromToConnect
A210.01.202331.01.20231
B320.01.202331.01.20231

 

I need the timetable, because this logic is applied to other tables at the same time too. So i cannot use a slicer on the Facts table itself or something like that. Other aspects, like how tables are connected, could be changed eventually.

 

Any thoughts on this? thank you so much 

5 REPLIES 5
H3nning
Resolver I
Resolver I

Thanks for your replies

 

@foodd you can see a sample pbix in the first answer. Unfortunately Im not sure if I can do it the way it is done there. There all rows are multiplied by the amount of dates involved. This is ok for a exaple like this. @Ashish_Mathur , how does this scale with a million rows in it?

Not sure.  Try that yourself.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

What I wanted to say is that i cannot do it that way. But thanks for your effort though 🙂

Ashish_Mathur
Super User
Super User

Hi,

Please find attached the PBI file.

Hope this helps.

Ashish_Mathur_0-1691117355674.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
foodd
Super User
Super User

This is difficult to visualize the state.  Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.