Forum Discussion

Joe_Marangos's avatar
Joe_Marangos
Frequent Visitor
4 years ago

Use Switch DAX to change between strings in a table visualisation

Hi all,

I am new to PowerBI and have been going through the process self taught, however, I have recently hit a bit of a brick wall. 

 

I am trying to make a legend for an interactive map I am building with Icon Map. I can get this to work when using individual columns. For example, dropping the "LegName" column into the table visualisation values section produces the following:

However, I have a slicer that switches data values on my map between two values that have a different scale and colour gradient. I tried writing the following DAX so that the slicer would switch  between the legend values in the table: 

 

 

 

 

 

Legend Cat = Switch(
 [SelectedMeasure],
 1,SELECTEDVALUE(YetAnotherTest[Custom.1],
 2,SELECTEDVALUE(NOXTestData[LegName])

 

 

 

 

 

However, I think I understand now that SELECTEDVALUE is not an appropriate function as it returns blank columns in the table:

I've been scowering the forums for sometime now to try and figure this out, but everything I seem to come accross is either only relative to numbers not strings, or I just can't seem to get to work.

 

I would greatly appreciate it if anyone could provide any solutions so that I can display strings like in the first screen shot, but be able to use the slicer to switch between different strings in the table so that the legend for each map can dynamicly update.

 

Many thanks,

Joe 

Need Help

3 Replies

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    As fas as I am able to interpret you requirement, you are looking for a way to setup interaction between slicer and your table.

    If there is no relationship between the two tables(NOXTestData & YetAnotherTest), you will have to associate the values separately

    Create a measure to contain the selected value of the slicer like this:

    Selected_Value = SELECTEDVALUE('Slicer Table'[Value])

    Now create a measure to associate your table with slicer:

     

    Dynamic value Calculation = 
SWITCH(
    TRUE(), 
    [Selected_Value] = "a", "Yourvaluecorresponding to a"),
    [Selected_Value] = "b", "Yourvaluecorresponding to b"),
    [Selected_Value] = "c", "Yourvaluecorresponding to c"),
    and so on.....
    )

     

    • Joe_Marangos's avatar
      Joe_Marangos
      Frequent Visitor
      4 years ago

      Thanks for the reply. Ill try to be a bit clearer in my followup. 

       

      For my slicer I have a disconected table called Parameter table which has the following info in it. 

      CodeName
      1NO2
      2NOX

       

      YetAnotherTest is my NO2 data and NOXTestData is my NOX data. They do have a relationship in a collumn called "NewID" which corresponds to the ID of the polygons on the map. I also already have a measure for the Parameter Table which is the same as you've used above:

       

       

       

      Selected Measure = SELECTEDVALUES('Parameter Table'[code],1)

       

       

       

      To switch between data values on the map I have a switch function that I put in the values section that is as follows: 

       

       

       

      All Pollution = SWTICH(
 [Selected Measure], 
 1,SELECTEDVALUE('YetAnotherTest'[NO2_Mean],
 2,SELECTEDVALUE('NOXTestData'[NOX_Mean]
)

       

       

       

      This means on my slicer, I can switch between values on the map. I had orignally assumed the same dax above would also work with the table visualisation. 

       

      I then created some conditional columns that I had intended to use in the legend. I am using a table to do this. One has the category the value sits in, the other the corresponding colour hex code. These are made with conditional columns. So for example, a value with NOX = 22 sits in the LegName = 20-23. 

       

      When I drop these columns into a table without a switch it works as shown: 

      I need the table to switch out the columns between the NO2 and NOX tables as the catergoies and hex code colours are different depending on whether data from YetAnotherTest or NOXTestData are selected in the slicer. 

       

      Unless, I'm missing something, I'm not sure the above is the direct sollution to this issue. I was hoping there was a more direct function, like the one used for switching values in the map. Does the fact that the tables are related by NewID provide any alternatives?

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Joe_Marangos 

        As a workaround, I think you could consider creating two separate table visualizations and then use the bookmark button to control which table visualization to show/hide.

         

        Best Regards,
        Community Support Team _ Eason