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

Question

Table 1: DigitalGroup No.DogWeight1Java141Bonnie202Tater92Sophie25&nbsp;Table 2: MechanicalGroup No.DogWeight1Java131Bonnie212Tater112Sophie29&nbsp;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.&nbsp;&nbsp;Weight = 
VAR Mechanical = ??
VAR Digital = ??
VAR Selection = SELECTEDVALUE('MethodType'[MethodType], Mechanical)
RETURN
SWITCH(TRUE (),
    Selection = "Mechanical", Mechanical,
    Selection = "Digital", Digital,
    Mechanical
)&nbsp;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.&nbsp;&nbsp;

anonymous · Accepted Answer

Hi&nbsp;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

Forum Discussion

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

2 Replies

Related content

Measure two columns

Measure or Calculated Column

Blank columns when doing Measure Name Measure Value approach

column vs measure

Measure not recognizing columns

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

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