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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Buckswriter
Frequent Visitor

FILTER / USERELATIONSHIP? Which will work best?

*NB* Before you read any further, please note that I've had to use my work credentials to log this request as Microsoft won't let me sign in to Power BI with my personal account, even though it will let me use Power BI Desktop version.

 

OK - I have created a set of tables that will enable me to create a league table based on the scores of football matches. The idea behind this has simply been for me to learn how to do so in Power BI, so that I can teach myself DAX more quickly (I use it at work also).

 

My tables are based on a single source table of matches and results, and it works fairly well, but...

 

What I would like to do is a find a way to filter the source table based on the dates of matches, so that I can display how the league stood at a given point in time, not just 'as at now', i.e. based on the total of ALL matches in the source table, or between two given dates, e.g. all the dates of a given month.

 

I can filter in Power Query to achieve this, but ideally I'd like to be able to employ a filter or similar within my visualisations to achieve this, rather than applying a filter in Power Query to achieve this outcome.

 

Any suggestions?

 

 

 

 

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

It's hard to say, but It doesn't look like the model is optimised. Are you sure you need all the physical tables? It seems many are actual calculations which ideally should be done with measures to include in visuals.

Ideally you should build dimension tables from the source table, create measures and build the visuals (as opposed to creating new physical tables to segment data). One of these dimension tables should be a date table with continuous dates covering the range of dates in the model. 

And it is highly recommended you avoid using many-to-many relationships.
Make sense?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Buckswriter
Frequent Visitor

Hi,

 

I've pasted below the model, I hope this helps. Put simply, 'North Fixtures' is the source table; the Home Scores and Away Scores tables convert the scores from 'North Fixtures' into a result for either the home or away team, and 'North Teams' pulls the results from the two tables back together in one, to arrive at a league table with points for wins and draws, calculating goals scored, conceded, etc.

 

What I would like to do is make it possible to filter North Teams based on date, e.g. limit the results that the tables reference to calculate the league table so that only matches played up to a certain date are taken into account, or only matches between two given dates are used in the calculations, etc - is this enough to go on? I don't know how to attach my pbix.Power BI National League.png

amitchandak
Super User
Super User

@Buckswriter , Hope I got it correctly.

If you have a date, then you can use date slicer to get data of date on table visual. You can use the before slicer to filter data till date.

 

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.