How to show a line chart and scatter chart in same visual having a special data model

[Anonymous]

Hello!

I am working on a special report that has some challenges. First I need to explain the data model before I go any further. I have a disconnected table A having static values (average weights, parameter 1, parameter 2, parameter 3). I have also a fact table B having average weights and parameter X, Y, Z and so, but also keys to dimensions.

Example of Table A which can be unpivoted (Type A-D). In the data model table A is LinechartData2_Unpivot

Avg weight	Type A	Type B	Type C	Type D
100	1,4	1,0	1,0	1,0
250	1,4	2,0	2,0	2,3
500	2,6	3,4	3,5	6,7
750	3,3	3,9	3,9	8,2
1000	3,9	4,3	4,5	9,9

Table B (can also be unpivoted on K-Factor, Fat and Pigment) 

Date Key	Project Name	Location	AvgWeight	ConditionFactor	Fat	Pigment	Cage	SampleDate
20191104	Project A	Location 10	4561	1,23747757470163	17,85	8,14	M2	2019-11-04 15:00:00.000
20191104	Project A	Location 10	4561	1,23747757470163	17,85	8,14	M4	2019-11-04 15:00:00.000
20191104	Project A	Location 10	4561	1,23747757470163	17,85	8,14	M1	2019-11-04 15:00:00.000
20191104	Project A	Location 10	4561	1,23747757470163	17,85	8,14	M3	2019-11-04 15:00:00.000
20191101	Project B	Location 20	2775	1,38929244615094	19,19	4,35	03	2019-11-01 10:35:36.000

For simplicity I have created simples dimensions with only one column. The dimensions are only related to table B (ScatterchartData2_Unpivot)

In the same visual I want to show data from the static table A as a line chart and a set of scatter charts from the table B. I want to select which line chart I want to use from one (disconnected) slicer, and also which scatter charts from an other disconnected slicer. AND I also want to select dimensions from other slicers to reduce data from table B

The visual has average weight on the X axis and Value on the Y axis. There are two problems here. 

1) how can I model these two tables in such way that I can select from the slicers like I described. I don't want to extend the static table A with dummy data to adjust it to table B and then join them.

2) how can I show the line chart and scatter plots in the same visual dynamically? I want to show one line chart and one or more scatter chart series in the visual.

I hoped I could add a demo pbix file to this post I don't know if that is possible. I add a picture in stead

Thanks to you all that spend time on me 

regards Geir

[Anonymous]

Hi @Anonymous ,

Nope, it is impossible to use normal visuals to achieve dynamic visual graphs in the same visual.

I'd like to suggest you create an R visual with if statement to plot line graph or scatter graph based on slicer selections.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello again.

Yes I have figured out that. I guess I can manage to use R. The thing there is how I can manage to dynamically add dataset.

But the other interesting thing here is how to data model the case. I have unrelated table and a related wider table but with common columns in average weight , attribute and value. I can create a calcualted table to append these dataset into one, but that means I have to extend the smaller dataset first. This will make my report rather messy when I want to filter on my dimensions. The result will be that I have a dummy element in all dimensions I need to selecet together with the actual element. I hope somebody know how to solve this data model issue

[Anonymous]

Hi @Anonymous,

I'd like to suggest you to use summarize function to create calculated tables to extract and aggregate related table fields. (use calculated table to pre-summarize related fields)
After these, you can extract original tables key fields and merge them to one as a bridge table to link these summarize tables. (build bridge table for relationship mapping and analysis)

In addition, if your data source are from SQL or other data sources that support to direct send 'T-SQL' query as optional parameters to get data.  I'd like to suggest you use 'T-SQL' query to summarize related fields to one for analysis on database side, it should have better performance than do it on power bi side.
Regards,

Xiaoxin Sheng

[Anonymous]

Obs How do I add a PBIX file to this post?