locked
dynamic column based on slicer selection RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am wondering is this to be done in Power Pivot.

    I would like to dynamically change columns in my pivot based on a slicer selection.

    Here is the data set (Table1). 

    Scroll right to see more

    Table1.

    manufacturer2 category category_v2 segment3 subsegment product_type brand4 bundle_regular price_segment size_ml size_range_ml benefit needscope gender Total sales_dol
    BELL FACE CARE FACE CARE TREATMENT TREATMENT ACNE BELL (BELL) REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED SAFE AND SIMPLE FEMALE 13.22303059
    BELL FACE CARE FACE CARE TREATMENT TREATMENT ACNE BELL (BELL) REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED SAFE AND SIMPLE FEMALE 12.69120471
    BELL FACE CARE FACE CARE TREATMENT TREATMENT ACNE BELL (BELL) REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED SAFE AND SIMPLE FEMALE 13.77625106
    BELL FACE CARE FACE CARE TREATMENT TREATMENT ACNE BELL (BELL) REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED SAFE AND SIMPLE FEMALE 8.393806588
    COSNOVA FACE CARE FACE CARE TREATMENT TREATMENT ACNE ESSENCE (COSNOVA) REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED SAFE AND SIMPLE FEMALE 0.970280941
    COSNOVA FACE CARE FACE CARE TREATMENT TREATMENT ACNE ESSENCE (COSNOVA) REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED SAFE AND SIMPLE FEMALE 0.958030588
    COSNOVA FACE CARE FACE CARE TREATMENT TREATMENT ACNE ESSENCE (COSNOVA) REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED SAFE AND SIMPLE FEMALE 0.016063529
    COTY FACE CARE FACE CARE TREATMENT TREATMENT ACNE MISS SPORTY REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED EXPRESSION OF VITALITY FEMALE 173.3629139
    COTY FACE CARE FACE CARE TREATMENT TREATMENT ACNE MISS SPORTY REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED EXPRESSION OF VITALITY FEMALE 96.04910188
    WIBO COLOR COLOR EYE COLOR MASCARAS NOT MAPPED WIBO REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED NOT MAPPED FEMALE 621.4200793
    WIBO COLOR COLOR EYE COLOR MASCARAS NOT MAPPED WIBO REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED NOT MAPPED FEMALE 460.8854024
    WIBO COLOR COLOR EYE COLOR MASCARAS NOT MAPPED WIBO REGULAR VALUE NOT MAPPED NOT MAPPED NOT MAPPED NOT MAPPED FEMALE 161.8382346

    I have created additional atribute table (Table2) to list column names coming from Table1

    attribute
    manufacturer2
    category
    category_v2
    segment3
    subsegment
    product_type
    brand4
    bundle_regular
    price_segment
    size_ml
    size_range_ml
    benefit
    needscope
    gender
    Total sales_dol

    Here is the output I need:

    a) when category is selected, I would like to have category in my pivot

    b) when segment3 is selected, I would like to have segment3 in my pivot, etc...

    Link to the file:

    link

    Thanks in advance



    Monday, January 15, 2018 11:02 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Bartek,

    Thanks for your response.

    It seems like a new idea for DAX, thanks for sharing this new idea here. Hopefully someone can share the solution for this question here.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 17, 2018 12:56 AM

All replies

  • Question
    Sign in to vote
    2
    Sign in to vote

    Hi Bartek,

    Thanks for your question.

    If I understand you correctly, you are tying to create dynamic table based on slicer selection, right?

    If so, I can not think of any ways to achieve this. Below is a article talking about dynamic column based on slicer selection, but not exactly what you are after:
    https://community.powerbi.com/t5/Community-Knowledge-Base/Dynamic-column-based-on-slicer-selection/ta-p/162635

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 16, 2018 1:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Willson,

    Yes, dynamic tables seems to be a right name. Thats what I need.

    The link you shared is not exactly I am after

    I will wait, maybe new ideas will come here


    Tuesday, January 16, 2018 9:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Bartek,

    Thanks for your response.

    It seems like a new idea for DAX, thanks for sharing this new idea here. Hopefully someone can share the solution for this question here.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 17, 2018 12:56 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Bartek,

    Thanks for your response.

    After several days testing and thinking, I found a workaround for this issue(Note: only test this in EXCEL 2016,you can do this same thing in Power Bi desktop).

    See below detailed steps:
    1) Select a cell in the table,then go to "Data--From Table":

    2) After step 1, you will open power query editor window, please go to the last column "Total sales_dol", right click on it, select "Unpivot Other Columns":

    3) Then go to "Close & Load--Close & Load", you will get new table in the new sheet

    4) Select the new table, then go to "Power Pivot--Add to Data Model"

    5) Add a sum measure called [Sum of Total sales_do2] in the Power Pivot data model,Then you can create a Power Piovt report what is you are after as below:

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 25, 2018 8:53 AM