Forum Discussion

Peter_Jeyaraj_I's avatar
Peter_Jeyaraj_I
Helper II
7 years ago
Solved

Derive Column based on Slicer selection

Hi All,    I want to change the dimension selection in a chart based on the slicer filter in POwer bi.. For ex.. i have a mapping with 2 different description per language like below     &...
Tips and Tricks
  • Peter_Jeyaraj_I's avatar
    Peter_Jeyaraj_I
    7 years ago

     thank you.. i created the many to many releationship by using the bridge table. 

    this way i created the relationship b/w my transaction to fail code mapping with duplicate entries ( 1 entry for eng and 1 entry for danish).  that solved my issue.. 

     

    more details refer below link

    https://www.seerinteractive.com/blog/join-many-many-power-bi/

     

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

Peter_Jeyaraj_I Could you please post your expected output as well ? If possible sample data that can be copied. 

  • Peter_Jeyaraj_I's avatar
    Peter_Jeyaraj_I
    Helper II
    7 years ago

    Here below i shared the sample screenshot

    I have 3 different tables

    1. Master ( Countof Failures measure available here )

    2. Fail_Mapping ( Fail category & fail category d columns available here)

    3. Language ( Lang field )

    If i select english then the chart has to show the failures count by fail category 

    If i select danish then the chart has to show the failures by fail category d. Hope this helps

     

    • PattemManohar's avatar
      PattemManohar
      Community Champion
      7 years ago

      Peter_Jeyaraj_I Here is the steps I've followed to solve this..

       

      Create a new calculated table from your main table as below:

       

      Test21ChartNew = UNION(
                        SELECTCOLUMNS(Test21Chart,"Code",[Code],"FailCategory",[FailCategory])
                       ,SELECTCOLUMNS(Test21Chart,"Code",[Code],"FailCategory",[FailCategory_D]) 
                      )

      The output will be as :

       

       

      Similary, create a lookup table as below

       

      Test21ChartSlicer = DISTINCT(
                UNION(
                        SELECTCOLUMNS(Test21Chart,"Language","English","FailCategory",Test21Chart[FailCategory])
                        ,SELECTCOLUMNS(Test21Chart,"Language","Danish","FailCategory",Test21Chart[FailCategory_D])
                    )
                )

       

      This will be result to as below:

       

       

      Now make sure these two tables have relationship on FailCategory field. That's it, now you have your output as expected.

       

       

       

      • Peter_Jeyaraj_I's avatar
        Peter_Jeyaraj_I
        Helper II
        7 years ago

        Thanks for your response. 

         

        but i need to maintain the unique values in fail code table as i have code - code relationship b/w master table( kind of transaction table) and fail code table. 

         

        that is the reason why i created new columns to accomadate other language values.