Building relationship between tables & time slicer with google analytics

[Anonymous]

I am trying to build a relationship between these two tables. 

In the Main-View Solutions table, each page title only appears once.

In the Solutions table, the same page title appears multiple times because this table has "date".

I am trying to connect these two so when I move the time slicer in the report, the data from the Main-View Solutions table will change accordingly. The slicer was built using the "date" in the Solutions table.

Thank you for your help in advance!

[TomMartens]

Hey @Anonymous ,

there is a simple solution to achive what you want, simply change the Cross-filter direction of the relationship in the above screen shot from Single to Both.

This simpe change comes with a price turns the "strong" 1-to-many relationship into a weak relationship and you might pay this simple change with an performance degradation. If you will notice this performance degradation depends on the size of your data model.

The change will also impact some DAX functions. For this reason it is considered a best practice to avoid the Cross-filter direction Both and instead create a dedicated measure in combination with the DAX function CROSSFILTER. Here you will find more information about the DAX function: https://dax.guide/crossfilter/

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

Hi Tom, 

Thanks for the help! I changed it from Single to Both, but it still doesn't work. Any ideas?

[TomMartens]

Hey @Anonymous ,

create a pbix that contains sample data, but still reflects your data model, and also the sample report.

Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom

[Anonymous]

https://drive.google.com/file/d/11gFPkgaH2w-GFEbBnLrY3oX1lA4DMo89/view?usp=sharing

Let me know if this works. Thanks a lot!

[TomMartens]

Hey @Anonymous ,

not sure what you are looking for, I consider this working, the date (from the many-side of the relationship) filters the one-side of the relationship, this is exactly what the Cross-filter direction Both does, this can be seen by the next two screenshots:

And the screenshot where the date (the table from the many side of the table) is used to filter the table from the one-side of the table:

If this is not working for you please check your version of Power BI Desktop, I'm using Power BI Desktop: Version: 2.96.701.0 64-bit (August 2021)

If instead, you are expecting that the value of the column "Unique pageviews" will change this will not happen, as the value represents "just" a simple value (at least in the pbix you shared with us).  If this column is created as a CALCULATED COLUMN using a DAX statement in the original file or inside Power Query, it is important to know that calculated columns (created using DAX) will only be re-calculated during data refresh and if the DAX statement changes, but not when a user interacts with the data by changing filters.

For this reason, it is important to create a measure. I use the following DAX statement to create a measure:

Count of rows = 
COUNTROWS( 'Solutions' ) 

Adding this measure to the table visual of your sample file, results to this:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

Hi Tom,

Thank you for all your help! Do you know how to make the number changes when people interact with the chart (in the Pdix I shared)? Examples below. 

I pulled the data directly from Google analytics. They are not calculated columns. Thanks again!

[Anonymous]

Hi Tom,

Thank you for all your help! Do you know how to make the number changes when people interact with the chart (in the Pdix I shared)? Examples below. 

I pulled the data directly from Google analytics. They are not calculated columns. Thanks again!

[TomMartens]

Hey @Anonymous ,

simply create the measure from my previous post, you can't make the value "Unique Pageviews" change, as this is a value that comes from Google Analytics, you have to consider this value as some kind of masterdata, and instead have to calculate this value yourself using a measure: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-measures

Hopefully, this helps to tackle your challenge.

Regards,

Tom