Forum Discussion

mjsystemss's avatar
mjsystemss
Resolver I
4 months ago
Solved

Help Making Column Headers (Dates) Dynamic in PBIX

Hello,   I am working on a Power BI Desktop Report Matrix with One Row and 4 Fields in Values Namely Activity          Lastweek      Weekly_Activity          Today        Percentage(%)            ...
Need Help
  • vojtechsima's avatar
    vojtechsima
    4 months ago

    Hey, mjsystemss ; you can do this nicely with field parameters with no additional setup (except the field parameters).

     

    Result would look like this:

    First, create the measures you need:

    Then create a Field Parameter:

    Give it a name and put the measures there (just put anything really, you can modify it later):

     

    Then go to Data pane; check out the new Field Parameter table and modify if it like this:

    Adjusted order and replace the Text string of the names of Today/LastWeek measures as I have.

    Dynamic_Matrix_Headers = {
    (FORMAT(TODAY() - 7, "dd-MM-yyyy"), NAMEOF('Fact_Activity'[Measure_Lastweek]), 0),
    ("Weekly_Activity", NAMEOF('Fact_Activity'[Measure_WeeklyActivity]), 1),
    (FORMAT(TODAY(), "dd-MM-yyyy"), NAMEOF('Fact_Activity'[Measure_Today]), 2),
    ("Percentage(%)", NAMEOF('Fact_Activity'[Measure_Percentage]), 3),
    ("Total_Amount", NAMEOF('Fact_Activity'[Measure_TotalAmount]), 4)
}

     

    Then put the Field Parameter's column to your Matrix:

    The name will probably be different, but it doesn't matter.

     

    Then you'll get this:

    Attaching PBI, so you can test it yourself.

Natarajan_M's avatar
Natarajan_M
Super User
4 months ago

Hi mjsystemss , From your query, I understand that you need dynamic column names in the table. Is my understanding correct?

I tried to recreate the scenario using the sample data , check whetehr the below approach works for you 


Sample Input :


Header table :

 


Sample OP :



MatrixValue = 
SWITCH(
    SELECTEDVALUE(MetricHeaders[MetricKey]),
    "LastWeek",
        CALCULATE(SUM(ActivityData[Amount]),
            ActivityData[Date] = TODAY() - 7),
    "Weekly_Activity",
        CALCULATE(SUM(ActivityData[Amount]),
            ActivityData[Date] >= TODAY() - 7 &&
            ActivityData[Date] <= TODAY()),
    "Today",
        CALCULATE(SUM(ActivityData[Amount]),
            ActivityData[Date] = TODAY()),
    "Percentage",
        DIVIDE(
            CALCULATE(SUM(ActivityData[Amount]), ActivityData[Date] = TODAY()),
            SUM(ActivityData[Amount]), 0),
    "Total_Amount",
        SUM(ActivityData[Amount]),
    BLANK()
)


Header table :

MetricHeaders = 
DATATABLE(
    "SortOrder", INTEGER,
    "MetricKey", STRING,
    {
        {1, "LastWeek"},
        {2, "Weekly_Activity"},
        {3, "Today"},
        {4, "Percentage"},
        {5, "Total_Amount"}
    }
)


Calculated column :

MetricLabel = 
SWITCH(
    MetricHeaders[MetricKey],
    "LastWeek",        "Last Week (" & FORMAT(TODAY()-7, "MMM DD, YYYY") & ")",
    "Weekly_Activity", "Weekly Activity",
    "Today",           "Today (" & FORMAT(TODAY(), "MMM DD, YYYY") & ")",
    "Percentage",      "Percentage (%)",
    "Total_Amount",    "Total Amount",
    MetricHeaders[MetricKey]
)


Pbix :
Today_Weekly.pbix

Thanks 
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!