Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Impactful Individual
4 years ago
Solved

dynamic column entry or workaround

Hello,

 

i have created the following table: 

 

New_Table =
DATATABLE (
"Column 1",STRING,
"Column 2 Order",INTEGER,
{
{ "Start",1 },
{ "Middle",2 },
{ "End",3 }
}
)
 
I use the string entries in the x-axis of a bar chart. 
 
Is it possible to create the String "Start" dynamic? For example if I filter "Plan" I want to see "Start Plan", if I filter FC I want to see "Start FC" and so on.
 
I tried a workaround with creating a small box with a dynamic measure text and push over the visual but i get some other problem in Power BI Service: If you move the cursor over the visual (or click first) the small box vanishes.  
 
Some other ideas?
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Pikachu-Power ,

     

    We could only add columns in X axis. As far as I know, Power BI doesn't support us to create a dynamic column based on filter. I suggest you to add Start Plan, Start FC into the data model and then calculate the measure for all of them. Then you can filter by X axis appear or disappear.

    My Sample:

    New_Table:

    New_Table = 
DATATABLE (
    "Column 1", STRING,
    "Column 2 Order", INTEGER,
    "Column 3", STRING,
    "Column 4", STRING,
    {
        { "Start", 1, "Start Plan", "Plan" },
        { "Middle", 2, "Middle Plan", "Plan" },
        { "End", 3, "End Plan", "Plan" },
        { "Start", 4, "Start FC", "FC" },
        { "Middle", 5, "Middle FC", "FC" },
        { "End", 6, "End FC", "FC" }
    }
)

    Measure:

    Measure = 
VAR _SUM = CALCULATE(SUM('Table'[Value]),FILTER('Table', 'Table'[Column1] = MAX(New_Table[Column 1])&&'Table'[Column4] = MAX(New_Table[Column 4])))
RETURN
IF(ISFILTERED(Column4[Column 4]),_SUM,BLANK())

    When I select Plan in slicer it will show all plan in axis.

     

    Best Regards,
    Rico Zhou

     

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Pikachu-Power ,

     

    We could only add columns in X axis. As far as I know, Power BI doesn't support us to create a dynamic column based on filter. I suggest you to add Start Plan, Start FC into the data model and then calculate the measure for all of them. Then you can filter by X axis appear or disappear.

    My Sample:

    New_Table:

    New_Table = 
DATATABLE (
    "Column 1", STRING,
    "Column 2 Order", INTEGER,
    "Column 3", STRING,
    "Column 4", STRING,
    {
        { "Start", 1, "Start Plan", "Plan" },
        { "Middle", 2, "Middle Plan", "Plan" },
        { "End", 3, "End Plan", "Plan" },
        { "Start", 4, "Start FC", "FC" },
        { "Middle", 5, "Middle FC", "FC" },
        { "End", 6, "End FC", "FC" }
    }
)

    Measure:

    Measure = 
VAR _SUM = CALCULATE(SUM('Table'[Value]),FILTER('Table', 'Table'[Column1] = MAX(New_Table[Column 1])&&'Table'[Column4] = MAX(New_Table[Column 4])))
RETURN
IF(ISFILTERED(Column4[Column 4]),_SUM,BLANK())

    When I select Plan in slicer it will show all plan in axis.

     

    Best Regards,
    Rico Zhou

     

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