Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Let's say I have a Stores table, with store, city and country and a Sales table, with store and sales and have a drill through page based on the Store field. I want a visual that shows sales of all stores in the same country of the store drilled.
===== edit ====
The visual should show the saled by city within the same country but not as an aggregation.
Is it possible?
Solved! Go to Solution.
I can offer you the next workaround.
1. create a table
Table =
ADDCOLUMNS(
'Sales Table', "Country",
CALCULATE(FIRSTNONBLANK('Store Table'[Country],1), FILTER(ALL('Store Table'),'Store Table'[Store]=EARLIER([Store])))
)
2. create a bridge Country table
bridgeCountry = DISTINCT('Store Table'[Country])
3 create relationships
4. put new table fields into chart
see my pbix in attach
Thank you for your response. But with your answer I recognize I wasn't very clear about what need. The visual should show the sales amount by all cities within the same country but not aggregated.
Sure:
Store table
Store | City | Country |
A | NY | USA |
B | LA | USA |
C | London | UK |
D | Paris | France |
Sales table
Store | Amount |
A | 100 |
B | 200 |
C | 400 |
D | 300 |
If I drill through Store A, for example, I want one bar chart visual that shows two data points: Store A and Store B as they are located in the same country as Store A. But since that visual is on the drill through page, it's only showing the sales of Store A. Not sure if that's more clear. Thanks for the time anyway.
I can offer you the next workaround.
1. create a table
Table =
ADDCOLUMNS(
'Sales Table', "Country",
CALCULATE(FIRSTNONBLANK('Store Table'[Country],1), FILTER(ALL('Store Table'),'Store Table'[Store]=EARLIER([Store])))
)
2. create a bridge Country table
bridgeCountry = DISTINCT('Store Table'[Country])
3 create relationships
4. put new table fields into chart
see my pbix in attach
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |