Looker Studio - Filter on 3 tables with relationship 1 to 0-Many

[Fls]

Hi all,

I have 3 tables :
- Products (Product_Id, Product_Name,Product_Name_Price, Product_Purchased_At, Product_Status)
- Products_UsedByZones (Product_Id, Zone_Id): 1 to 0..Many
- Products_Categories (Product_Id, Category_Id): 1 to 0..Many

I would like to display several info, sum, count, and graph on Products based on the following filters:
- Zone_Id (select 1 or several zones). It should be dynamic based on data. Not a predefined list of values.
- Category_Id (select 1 or several zones). It should be dynamic based on data. Not a predefined list of values.
- Product_Status
- Product_Purchased_At
We should have the possibility to set 1 or several filters to display the expected results.

In Looker Studio, we configured data sources, controls, and charts.
Key Columns in my tables have the same name but it doesn't work in Looker Studio, even when cross filtering is enable everywhere.
I work on it since several hours with several Looker experts. I don't understand why such simple need can't be done in Looker Studio.

Would you have a solution, please? 🙂

Best regards,
Florent.

[ArkadyZagdan]

Imo you should be seeking to display data from one signle table.
Joining it on product_ID and then feeding into looker studio from one table should fix it all.

[Fls]

Hi,
Thanks but it doesn't work.

Effectively, joining allows filtering on Zone and category.
But it duplicates lines. So all indicators are wrong. Only one indicator is correct:  count(distinct product_id).
Sum on prices, count per status are wrong as will count several time the price for the same product if the product has several category and zone.
And Looker doesn't provide analytics functions.

I found a workaround by using the CSV Filter Control (a control developed by community).
For each product, I concatenated the academies (resp. programs) in a column.
So I have one table with one line per product.

I'm still disappointed that such feature -a real cross-filtering upon joined tables- is not available in Looker.

My company is thinking to migrate all our reports to PBI in which it's easy to manage such case.

Best regards.

[ArkadyZagdan]

This is whole idea of Looker Studio and an advantage over PBI.
As a general rule of thumb you should always do transformation in the source. And ideally it should be data warehouse. It takes aways your computing resources from the cache of your loccal machine into the virtual machine which will effectively fasten interaction when you deal with LARGE ammount of data, especcially when powered by BigQuery BI Engine.

And it is a great practice to transform the data in the source when you are using a source designed for it, which is data warehouse and not transactional database for example, which indeed is a bad practice.

Bottom line. PBI vs LS has different aproach. You want to do fancy things, you go to BigQuery, do your transformations there and then visualise the data with a minimum of a latency for final users.

[HankinsonC]

1. Calculate the metric on the source.
2. blend. 
3. the chart will return the metric as a field
4. To get the original metric back, take the average of that field.

This works for most use cases but not all. 

[Fls]

Hi,

Thank you for your help.
Impossible in my case to calculate several metrics (sum, count, ratio) in advance depending on two dimensions with 0toN relationships and 5 additional simple filters (product name, date etc.).

Moreover, it's not a data good practice to transform data at the source.

It seems the only correct answer is to change for a reel and effective dataviz solution.
PBI do it very simply. You may have filters based on one or several "dimension" tables that cross-filter the "fact" table.

Have a nice day.

[HankinsonC]

The "source" I am referring to is the source in Looker Studio in this context and NOT a transformation. You can introduce metrics as calculated fields. Please note that calculations on the "source" here are done by LS.