FILTER / USERELATIONSHIP? Which will work best?

[Buckswriter]

*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?

[PaulDBrown]

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?

[Buckswriter]

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.

[amitchandak]

@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.