Click behavior changes when question added to dashboard

[Shaun]

I'm sorry if this question has been asked/answered before. I'm new to Metabase. I'm running on version v0.43.2.

I have a question that lists all transactions - Transactions List.
I then have another question that summarizes amounts in the Transactions List, based on currency pair, e.g. USD.GBP (Dollar vs Pound).

If I click on an amount in the Position column, then click on the option to View These Transactions List, Metabase correctly shows me ONLY the underlying Transactions, filtered on the currency pair.

But if the question is added to a Dashboard, when clicking on an amount in the Position column, to View These Transactions List, it shows ALL of the underlying transactions. The filter by Ccy Pair is lost.

Can someone point out what might be my problem?

Thanks very much... Shaun

[flamber]

Hi @Shaun
I'm not quite sure what is going on, but are you using Click Behavior (https://www.metabase.com/learn/dashboards/custom-destinations) or the built-in drill-through (https://www.metabase.com/learn/questions/drill-through) ?
Try re-creating your question in the Sample Database, which is included in Metabase, then we can all reproduce the problem.

[Shaun]

Hi @flamber,

Thanks for getting back to me so rapidly. I think it is just the Drill Through - I've not configured anything. See the screenshot below:

I'll have a go at replicating the issue with the Sample database.

Cheers... Shaun

[flamber]

@Shaun Try including the question metadata by going to the URL /api/card/123, where 123 is the question ID of "Currency Pair Positions" (you can redact the creator/updated email/name).
That should give me enough information to try to reproduce the problem.

[Shaun]

Thanks @flamber,

Please find the metadata below. All help is most appreciated - especially on a Saturday!

Cheers... Shaun

{"description":"Currency Pair Positions","archived":false,"collection_position":null,"table_id":10,"result_metadata":[{"description":null,"semantic_type":"type/Category","coercion_strategy":null,"name":"Code","settings":null,"field_ref":["field",230,{"source-field":110}],"effective_type":"type/Text","id":230,"display_name":"BankRateId → Code","fingerprint":{"global":{"distinct-count":9,"nil%":0.6521739130434783},"type":{"type/Text":{"percent-json":0,"percent-url":0,"percent-email":0,"percent-state":0,"average-length":2.4347826086956523}}},"base_type":"type/Text"},{"display_name":"Sum of Total","semantic_type":null,"field_ref":["aggregation",0],"name":"sum","base_type":"type/BigInteger","effective_type":"type/BigInteger","fingerprint":{"global":{"distinct-count":4,"nil%":0},"type":{"type/Number":{"min":-26000,"q1":-13500,"q3":31000,"max":50000,"sd":31700.420607093947,"avg":8750}}}}],REDACTED,"last_login":"2022-06-10T07:18:46.459","is_qbnewb":false,"is_superuser":true,"id":1,"last_name":"ADVENT","date_joined":"2022-06-09T12:29:04.161","common_name":"ETrade ADVENT"},"can_write":true,"database_id":2,"enable_embedding":false,"collection_id":null,"query_type":"query","name":"Currency Pair Positions","last_query_start":"2022-06-11T20:08:56.285+07:00","dashboard_count":1,"average_query_time":115,"creator_id":1,"moderation_reviews":[],"updated_at":"2022-06-11T17:55:52.272","made_public_by_id":null,"embedding_params":null,"cache_ttl":null,"dataset_query":{"database":2,"query":{"source-table":"card__1","aggregation":[["sum",["field","Total",{"base-type":"type/BigInteger"}]]],"breakout":[["field",230,null]],"filter":["and",["=",["field",102,null],"Done"],["time-interval",["field",104,null],1,"day",{"include-current":true}]]},"type":"query"},"id":296,"display":"table","visualization_settings":{"pivot_table.column_split":{"rows":[["field",230,{"source-field":110}]],"columns":[["field",92,null]],"values":[["aggregation",0]]},"table.pivot":true,"table.pivot_column":"BidAsk","table.cell_column":"sum","column_settings":{"["ref",["field",230,{"source-field":110}]]":{"column_title":"Ccy Pair"},"["name","sum"]":{"column_title":"Position"}}},"collection":null,"dataset":false,"created_at":"2022-06-11T16:48:56.52","public_uuid":null}

[Shaun]

Hi again @Flamber,

I have reproduced the problem using the Sample Database.

When drilling down on the question it works fine

But when the question is put on a dashboard, it brings up ALL Orders.

Let me know if you would like the URLs or anything else.

Many thanks... Shaun

[flamber]

@Shaun Can you include the metadata of the two Sample questions? Both "Orders with proper values" and "Summarized Totals by Product".
And please only redact the email/name, not the actual format of the JSON, since that is what I need to reproduce.

[Shaun]

Hi @flamber,

Thanks for your continued support. I've redacted identifying details.

Orders with proper values:

{"description":null,"archived":false,"collection_position":null,"table_id":2,"result_metadata":[{"description":"This is a unique ID for the product. It is also called the “Invoice number” or “Confirmation number” in customer facing emails and screens.","semantic_type":"type/PK","coercion_strategy":null,"name":"ID","settings":null,"field_ref":["field",17,null],"effective_type":"type/BigInteger","id":17,"display_name":"ID","fingerprint":null,"base_type":"type/BigInteger"},{"description":"The name of the product as it should be displayed to customers.","semantic_type":"type/Title","coercion_strategy":null,"name":"TITLE","settings":null,"field_ref":["field",5,{"source-field":13}],"effective_type":"type/Text","id":5,"display_name":"Product → Title","fingerprint":{"global":{"distinct-count":199,"nil%":0.0},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":21.495}}},"base_type":"type/Text"},{"description":"The name of the user who owns an account","semantic_type":"type/Name","coercion_strategy":null,"name":"NAME","settings":null,"field_ref":["field",22,{"source-field":11}],"effective_type":"type/Text","id":22,"display_name":"User → Name","fingerprint":{"global":{"distinct-count":2499,"nil%":0.0},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":13.532}}},"base_type":"type/Text"},{"description":"The raw, pre-tax cost of the order. Note that this might be different in the future from the product price due to promotions, credits, etc.","semantic_type":null,"coercion_strategy":null,"name":"SUBTOTAL","settings":null,"field_ref":["field",14,null],"effective_type":"type/Float","id":14,"display_name":"Subtotal","fingerprint":{"global":{"distinct-count":340,"nil%":0.0},"type":{"type/Number":{"min":15.691943673970439,"q1":49.74894519060184,"q3":105.42965746993103,"max":148.22900526552291,"sd":32.53705013056317,"avg":77.01295465356547}}},"base_type":"type/Float"},{"description":"This is the amount of local and federal taxes that are collected on the purchase. Note that other governmental fees on some products are not included here, but instead are accounted for in the subtotal.","semantic_type":null,"coercion_strategy":null,"name":"TAX","settings":null,"field_ref":["field",16,null],"effective_type":"type/Float","id":16,"display_name":"Tax","fingerprint":{"global":{"distinct-count":797,"nil%":0.0},"type":{"type/Number":{"min":0.0,"q1":2.273340386603857,"q3":5.337275338216307,"max":11.12,"sd":2.3206651358900316,"avg":3.8722100000000004}}},"base_type":"type/Float"},{"description":"The total billed amount.","semantic_type":null,"coercion_strategy":null,"name":"TOTAL","settings":null,"field_ref":["field",15,null],"effective_type":"type/Float","id":15,"display_name":"Total","fingerprint":{"global":{"distinct-count":4426,"nil%":0.0},"type":{"type/Number":{"min":8.93914247937167,"q1":51.34535490743823,"q3":110.29428389265787,"max":159.34900526552292,"sd":34.26469575709948,"avg":80.35871658771228}}},"base_type":"type/Float"},{"description":"Discount amount.","semantic_type":"type/Discount","coercion_strategy":null,"name":"DISCOUNT","settings":null,"field_ref":["field",9,null],"effective_type":"type/Float","id":9,"display_name":"Discount","fingerprint":{"global":{"distinct-count":701,"nil%":0.898},"type":{"type/Number":{"min":0.17088996672584322,"q1":2.9786226681458743,"q3":7.338187788658235,"max":61.69684269960571,"sd":3.053663125001991,"avg":5.161255547580326}}},"base_type":"type/Float"},{"description":"The date and time an order was submitted.","semantic_type":"type/CreationTimestamp","coercion_strategy":null,"unit":"default","name":"CREATED_AT","settings":null,"field_ref":["field",12,{"temporal-unit":"default"}],"effective_type":"type/DateTime","id":12,"display_name":"Created At","fingerprint":{"global":{"distinct-count":9998,"nil%":0.0},"type":{"type/DateTime":{"earliest":"2016-04-30T18:56:13.352Z","latest":"2020-04-19T14:07:15.657Z"}}},"base_type":"type/DateTime"},{"description":"Number of products bought.","semantic_type":"type/Quantity","coercion_strategy":null,"name":"QUANTITY","settings":null,"field_ref":["field",10,null],"effective_type":"type/Integer","id":10,"display_name":"Quantity","fingerprint":{"global":{"distinct-count":62,"nil%":0.0},"type":{"type/Number":{"min":0.0,"q1":1.755882607764982,"q3":4.882654507928044,"max":100.0,"sd":4.214258386403798,"avg":3.7015}}},"base_type":"type/Integer"}],"creator":{"email":"REDACTED","first_name":"REDACTED","last_login":"2022-06-10T07:18:46.459","is_qbnewb":false,"is_superuser":true,"id":1,"last_name":"REDACTED","date_joined":"2022-06-09T12:29:04.161","common_name":"REDACTED"},"can_write":true,"database_id":1,"enable_embedding":false,"collection_id":1,"query_type":"query","name":"Orders with proper values","last_query_start":"2022-06-11T21:33:32.576+07:00","dashboard_count":0,"average_query_time":215,"creator_id":1,"moderation_reviews":[],"updated_at":"2022-06-11T21:33:32.879","made_public_by_id":null,"embedding_params":null,"cache_ttl":null,"dataset_query":{"type":"query","query":{"source-table":2,"order-by":[["desc",["field",13,null]]],"fields":[["field",17,null],["field",5,{"source-field":13}],["field",22,{"source-field":11}],["field",14,null],["field",16,null],["field",15,null],["field",9,null],["field",12,{"temporal-unit":"default"}],["field",10,null]]},"database":1},"id":297,"display":"table","last-edit-info":{"id":1,"email":"REDACTED","first_name":"REDACTED","last_name":"REDACTED","timestamp":"2022-06-11T20:25:54.972+07:00"},"visualization_settings":{"table.columns":[{"name":"ID","fieldRef":["field",17,null],"enabled":true},{"name":"TITLE","fieldRef":["field",5,{"source-field":13}],"enabled":true},{"name":"USER_ID","fieldRef":["field",11,null],"enabled":false},{"fieldRef":["field",22,{"source-field":11}],"enabled":true},{"name":"SUBTOTAL","fieldRef":["field",14,null],"enabled":true},{"name":"TAX","fieldRef":["field",16,null],"enabled":true},{"name":"TOTAL","fieldRef":["field",15,null],"enabled":true},{"name":"DISCOUNT","fieldRef":["field",9,null],"enabled":true},{"name":"CREATED_AT","fieldRef":["field",12,{"temporal-unit":"default"}],"enabled":true},{"name":"QUANTITY","fieldRef":["field",10,null],"enabled":true}],"table.pivot_column":"TITLE","table.cell_column":"SUBTOTAL"},"collection":{"authority_level":null,"description":null,"archived":false,"slug":"REDACTED","color":"#31698A","name":"REDACTED","personal_owner_id":1,"id":1,"location":"/","namespace":null},"dataset":false,"created_at":"2022-06-11T20:25:54.965","public_uuid":null}

and

Summarized totals by Product:

{"description":null,"archived":false,"collection_position":null,"table_id":2,"result_metadata":[{"description":"The name of the product as it should be displayed to customers.","semantic_type":"type/Title","coercion_strategy":null,"name":"TITLE","settings":null,"field_ref":["field",5,{"source-field":13}],"effective_type":"type/Text","id":5,"display_name":"Product → Title","fingerprint":{"global":{"distinct-count":199,"nil%":0},"type":{"type/Text":{"percent-json":0,"percent-url":0,"percent-email":0,"percent-state":0,"average-length":21.495}}},"base_type":"type/Text"},{"display_name":"Sum of Total","semantic_type":null,"settings":null,"field_ref":["aggregation",0],"name":"sum","base_type":"type/Float","effective_type":"type/Float","fingerprint":{"global":{"distinct-count":199,"nil%":0},"type":{"type/Number":{"min":153.99035733680418,"q1":420.84291416936856,"q3":949.9020237439129,"max":2503.1908216729807,"sd":411.86701103422865,"avg":725.3091085322744}}}}],"creator":{"email":"REDACTED","first_name":"REDACTED","last_login":"2022-06-10T07:18:46.459","is_qbnewb":false,"is_superuser":true,"id":1,"last_name":"REDACTED","date_joined":"2022-06-09T12:29:04.161","common_name":"REDACTED"},"can_write":true,"database_id":1,"enable_embedding":false,"collection_id":1,"query_type":"query","name":"Summarized Totals by Product","last_query_start":"2022-06-11T21:35:38.93+07:00","dashboard_count":1,"average_query_time":104,"creator_id":1,"moderation_reviews":[],"updated_at":"2022-06-11T20:33:38.976","made_public_by_id":null,"embedding_params":null,"cache_ttl":null,"dataset_query":{"type":"query","query":{"source-table":"card__297","aggregation":[["sum",["field",15,null]]],"breakout":[["field",5,null]],"filter":["not-null",["field",9,null]]},"database":1},"id":298,"display":"table","last-edit-info":{"id":1,"email":"REDACTED","first_name":"REDACTED","last_name":"REDACTED","timestamp":"2022-06-11T20:33:38.98+07:00"},"visualization_settings":{"table.pivot_column":"TITLE","table.cell_column":"sum"},"collection":{"authority_level":null,"description":null,"archived":false,"slug":"REDACTED","color":"#31698A","name":"REDACTED","personal_owner_id":1,"id":1,"location":"/","namespace":null},"dataset":false,"created_at":"2022-06-11T20:31:23.075","public_uuid":null}

[Shaun]

Please let me know if I've redacted too much.

Thanks

[flamber]

@Shaun Okay, we're not handling the implicit join correctly when parsing it as a parameter.
I've created an issue for it:
https://github.com/metabase/metabase/issues/23293 - upvote by clicking on the first post

You should be able to either use an explicit joined table in the first question or do remapping:
https://www.metabase.com/docs/latest/administration-guide/03-metadata-editing.html#remapping-column-values

[Shaun]

Thanks very much @flamber,

Really appreciate your help figuring out what's going on.

I'll try out your suggestions tomorrow. Enjoy the rest of your day.

All the best... Shaun

[Shaun]

Hi @flamber,

Per your suggestion I did remapping of the underlying data. I then redid the questions over the amended tables.

The drill-through now worked correctly from the Positions by Currency Pair Question and when it was placed on a Dashboard.

The only issue I have now is that when I drill down, the filter is showing the Currency Pair ID (jumble of characters) related to the Currency Pair. Can you advise if there's a way to show EUR.VND instead of the ID?

Thanks again for your help. Cheers... Shaun

[flamber]

@Shaun You are essentially seeing this issue, which is quite difficult to implement:
Display Nested Foreign Key Remappings · Issue #10945 · metabase/metabase · GitHub - upvote by clicking on the first post

Use explicit join instead:

[Shaun]

Thanks @flamber,

I'll try using the explicit join as suggested and see how that looks.

I guess I have to undo my remapping work to prevent any confusion.

All the best... Shaun

[flamber]

@Shaun Yes, it's better to avoid remapping in your case then. We are working on Models, which would allow more control over your ETL, but it's not quite ready:
https://www.metabase.com/docs/latest/users-guide/models.html

[Shaun]

Thanks again @flamber,

I'm getting closer through using the explicit join:

The table is displaying correctly.

It's showing the Filter correctly when drilling through - but for some reason it still wants to show me the Ccy Pair ID in the list of underlying transactions.

I'll try to figure it out. Getting brain-ache!

Cheers... Shaun

[flamber]

@Shaun I think you forgot to remove the "Ccy Pair ID" from your question and display the "Ccy Pair Code" instead. Probably comes from earlier attempts.

[Shaun]

Thanks @flamber,

I'll double check. You're probably right.

Thanks again... Shaun

[Shaun]

Hi @flamber,

I reworked the questions and I now have mostly resolved the problems.

I created one question - Order List, which uses a join to the Bank Rates table to pick up the currency code. Using the visualization feature I can place the currency pair in the appropriate column in the table.

I then created another question using the Order List question as my starting point.

(Some data has weirdly gone missing from the database (USD.VND currency pair code has gone!).

Drill through works :

But the Ccy Pair detail is far over to the right. So it's not following visualization rules for the underlying question - Order List.

Should I assume that there is no way to get exactly what I want? If I need to raise another issue, rather than flow on from previous concerns, please let me know.

Thanks very much for all your help.

Cheers... Shaun

[flamber]

@Shaun That's the linked issue in the issue I created. Basically we don't parse visualization settings, since it causes permission errors. Can't have both worlds currently.

Create a View on your database, so you avoid the nested question. That is the only solution currently.