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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.