Using Slicers to filter visualisations with data from multiple fields

[AntWickenden]

Hi there...

I am pretty new to PowerBi but am finding it very useful, however I have a problem, which is probably a simple fix, but despite much searching I am unable to find the solution for.

I have a dashboard with a number of visualisations...

Line Chart x 1

Matrix x 1

Slicers x 4

Card x 1

Which are fed by 3 data fields

Branch Rev Data

Import Data

Revenue GCI List

The Line Chart, Matrix & Slicers are fed by the "Branch Rev Data" field

The Card is fed by the "Import Data" field.

2 of the slicers are "Month" & "Year"

When I filter using the "Month" & "Year" slicers, I cannot get both slicers to impact what is displayed by the Card.

By adjusting the relationship between the "Branch Rev Data" and the "Import Data" fields I am able to have either the "Month" or "Year" work individually, but I can not have both "Month" and "Year" work together.

The Card needs to pull through a count of "Import File Reference" from the "Import Data" field but I want this to change depending on use of the Month & Year slicers.

I hope I have explained this OK.

Thanks in advance

Anthony

[Anonymous]

I think that an image of your model could help us to understand better the relationships between your tables.

Are Branch Rev Data and Import Data two different fields of the same table? Is it correct?

Have you a calendar table?

Year and month slicers are built on the calendar table?

[AntWickenden]

Hi @Anonymous

Thanks for your reply... i have been trying a number of solutions hence the delay in my reply but have still not got anywhere... so I am back to the original version of my query.

In reply to your questions...

Branch Rev Data and Import Data are 2 different tables...

There is no calendar table.

The raw Branch Rev Data source does not have any fields which are specifically a date, the report contains a Month field, a Year field and a Year & Month field but to my understanding these are not specifically linked to any date field.

The raw Import Data source does contain date information held in the INV field, then there is a Year field generated from the INV field. There is no month field in the example I have given. However I have added Month in another version but it still did not seem to work as expected.

There is a GCI List table which provides a list of the unique GCI numbers. Both tables are linked to the GCI List through the Revenue GCI Number field in the Branch Rev Data report and the Revenue GCI field in the Import Data table.

Moving on to the Visualisation view

The Master Client Card, Line Chart, Matrix and the 3 slicers related to the Revenue Names & Numbers are all fed by the Branch Rev Data table.

The Count Of Import File Card is fed by the Import Data table.

The Month and Year slicers are fed by the Branch Rev Data table.

When the Month & Year slicers are utilised, everything except the Count of Import file card filters correctly.

The Count of Import File does change, but not correctly. for example the Import Data table only contains data from 2018 but if you filter the Year slicer to 2016, the Count of Import file changes to 27.25K when it should be blank.

[Anonymous · Accepted solution]

Hi,

can the same GCI Number be present for different years?

By design, in your model, when you filter on year and month, you filter the GCI Number in the table Branch Rev Data and then you filter the GCI List by CGI Number and with this list you filter data in the Import Data where it calculate the measure Count of Import files. This measure depends by the list of the GCI Number filtered in GCI List, not directly by Year and month in Branch Rev Data.

In my opinion you have to build in power query a new table Years, with distinct year values by the two raw tables and create two relationship one-to-many from this table to the raw tables and build the year slicer on this new table. 

[AntWickenden]

Hi @Anonymous 

Many thanks... that seems to have worked to some extent...

Now after rebuilding the Year slicer using the newly added Year table, the other visualisations now filter as planned.

However, the Month slicer still does not affect the Count Of Import Files. It filters all the other visualisation but not the Count Of Import Files

[v-chuncz-msft]

@AntWickenden ,

You may check if the post below helps.

https://community.powerbi.com/t5/Desktop/composite-primary-key/td-p/307604