Measure to switch between table columns on visual: returns entire column rows

[smore]

Table 1: Digital

Group No.	Dog	Weight
1	Java	14
1	Bonnie	20
2	Tater	9
2	Sophie	25

Table 2: Mechanical

Group No.	Dog	Weight
1	Java	13
1	Bonnie	21
2	Tater	11
2	Sophie	29

I have the above two data tables. I can display the data in a scatter chart with the Y axis being the Group No. and the X axis being the Weight which results in 2 bubbles showing up for either group. However, I want to be able to slice between the weight columns for Table 1 or Table 2. So, if I select Table 1 data in the slicer, only the Table 1 Weight values are displayed and vice versa. 

Weight = 
VAR Mechanical = ??
VAR Digital = ??
VAR Selection = SELECTEDVALUE('MethodType'[MethodType], Mechanical)
RETURN
SWITCH(TRUE (),
    Selection = "Mechanical", Mechanical,
    Selection = "Digital", Digital,
    Mechanical
)

I have the above measure which switches based on slicer selection of Mechanical or Digital but I do not know what the VAR for either Mechanical or Digital should be as I want it to return all the Weight values in the column rather than a single scalar such as SUM() or MAX() would do.  

[Anonymous · Accepted solution]

Hi @smore,

I'd like to suggest you extract and merge the 'no' and 'dog' fields to create a new table with unique value as the bridge link to those tables.

How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

Relationship in Power BI with Multiple Columns - RADACAD

After these steps, you can create a parameter table with two table names as the source of the slicer, then you can write a measure filter to redirect to different calculation formulas based on selections.

Weight Measure =
VAR currDogNO =
    SELECTEDVALUE ( Bridge[DogNO] ) //DogNo are merge from 'Group NO' and 'Dog' field values
VAR Selection =
    SELECTEDVALUE ( 'Selector'[Source] )//Selecotr with two table Names
RETURN
    SWITCH (
        Selection,
        "Mechanical",
            CALCULATE (
                SUM ( Mechanical[Weight] ),
                FILTER ( ALLSELECTED ( Mechanical ), Mechanical[DogNO] = currDogNO )
            ),
        "Digital",
            CALCULATE (
                SUM ( Digital[Weight] ),
                FILTER ( ALLSELECTED ( Digital ), Digital[DogNO] = currDogNO )
            )
    )

Regards,

Xiaoxin Sheng

[amitchandak]

@smore , You need to create two dimensions/common tables  Group No and Dog and join them with both tables and use for filter/slicer 

example new table

group

distinct(union(distinct(Digital[group No]),distinct(Mechanical[group No])))

same way for Dog

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/