Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
smore
Helper I
Helper I

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

Table 1: Digital

Group No.DogWeight
1Java14
1Bonnie20
2Tater9
2Sophie25

 

Table 2: Mechanical

Group No.DogWeight
1Java13
1Bonnie21
2Tater11
2Sophie29

 


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.  

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@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/

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.