Forum Discussion

Bhavana_k's avatar
Bhavana_k
Helper I
7 years ago

Dynamic Dimension in Power BI

Hi,

 

I have a one common chart where i'll be showing sales but this sales value should be displayed as per selected dimension
my dimensions are : project or product.

This is what I have done:
Step1: I created one table in power bi named it as a dimension. And created 2 columns Dimension and Index.


Step2: Created calculated measure like below

Show Measure =
IF(ISCROSSFILTERED('Dimension'[Dimension]),
SWITCH(TRUE(),
VALUES('Dimension'[Dimension])="Project",MAX(Table1[PROJECTNAME]),
VALUES('Dimension'[Dimension])="product",MAX(Table1[PRODUCT]),
BLANK()),BLANK())

Step3 : Created slicer On Dimension column from Dimension Table.

Now what I want to do, when i select Project it should show me list of all projects with sales value and when I select Product then it should show me changed sales value for all products.

 But in above calculated measure to get that column name I have to wrap it around some aggregation function. As you can see I am using MAX function to wrap my Text column which doesn't make any sense. But it shows only first product name or project name, neither list of all projects nor list of all products.

How do I show that? How do i make this flip flop work?

Thank you,
Bhavana



Interesting Links
Need Help
Show and Tell

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Bhavana_k ,

     

    Based on my test, it is not supported to change the column names based on the filter selection dynamically in Power BI currently. You can post your new idea here https://ideas.powerbi.com/forums/265200-power-bi-ideas , to improve the Power BI.

     

    It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

     

    To work around the issue, you can create a calculated table like DAX below, then enter into Query Editor, click on the column Project and Product at the same time, right click to choose the "Unpivot columns", don't forget to click the "Close & Apply" button.

    Then create relationships between this new intermediate table and other two tables on Project field and Product field, After that, you can let the result Attribute column as source of slicer visual.

     

    New table= CROSSJOIN(SELECTCOLUMNS('Project Table', "Project ", 'Project Table'[Project]),SELECTCOLUMNS('Product Table', "Product ", 'Product Table'[Product]))

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Bhavana_k's avatar
      Bhavana_k
      Helper I
      7 years ago

      Hi Amy,

       

      Thank you for getting back.

      But my data is huge and by doing this pivot thing it will increase my no. of rows. Which is not a very good scenario for my case. 
      Surely I'll keep this in mind.

       

      Thank you,

      Bhavana

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi  Bhavana_k ,

     

    Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards

    Amy