Dynamically columns select in table visual

[Suman2585]

Hi All,

I need help to Dynamically select columns in table visual.

My requirement is we have data in columns like Sep-23, Oct-23, Nov-23, Dec-23, Jan-24, Feb-24, Mar-24, Apr-24, May-24, Jun-24, Jul-24, Aug-24. for 12 months data. For next month Sep-23 will remove automatically and Oct-24 month data need to add in table visual automatically 

And also it has to sum all 12 months data automatically for next month.

I tried unpivot columns option but it is not working.

Below is the sample data set for your reference.

[ChiragGarg2512]

The columns are not dynamically changeable in table visual as these are the parameters that are added to the visual. What can be done for a small data is to transpose the data. This will make products as column and month as rows. Now, make a measure of each product and month.

For transpose, 

1) Make header the first row

2) Transpose[Transform in Power Query -> Transpose]

3) Make first Row as headers

[123abc]

Revised Answer:

To dynamically select columns in a table visual and automatically handle the addition and removal of columns based on your requirements, you can achieve this in Power BI by using DAX expressions and data modeling. Here's a step-by-step guide on how to do it:

1. Data Preparation:

   • Ensure your data source includes a Date column and a Value column.
   • Create a Date table with a continuous sequence of dates covering the entire range of months you expect in your dataset.

2. Data Modeling:

   • Create a relationship between your main data table and the Date table using the Date column.
   • Create a calculated column that generates a flag for the 12 months you want to display. You can use the following DAX expression as an example:

Use Dax:

Display Month Flag =
IF(
AND(
[Date] >= TODAY(),
[Date] < TODAY() + 365,
MONTH([Date]) <= MONTH(TODAY()) + 12
),
1,
0
)

1. This calculated column will evaluate to 1 for the 12 months starting from the current month and 0 for other months.

2. Table Visual:

   • Create a table visual.
   • In the Values field well of the table visual, place your Value column.
   • In the Rows or Columns field well of the table visual, place your Date column from the Date table.

3. Visual-Level Filter:

   • Apply a visual-level filter to your table visual by setting the "Display Month Flag" to 1.

Now, your table visual should dynamically display the data for the current month plus the next 11 months, automatically removing the oldest month as time progresses.

Additionally, to automatically sum the data for the displayed 12 months, you can use another DAX measure:

Use Dax:

Total 12 Months Value = SUMX(FILTER('YourData', [Display Month Flag] = 1), 'YourData'[Value])

You can place this measure in your table visual, and it will show the sum of the displayed 12 months' data.

Remember to replace 'YourData' with the name of your actual data table.

By following these steps and creating calculated columns and measures based on the "Display Month Flag," you can achieve the dynamic column selection and summation in your table visual as per your requirements.

and 

If you want to dynamically change the columns in a table visual based on your data and have products as columns and months as rows, you can indeed use the Power Query's Transpose feature. Here's how you can do it:

1. Data Preparation:

   • Ensure you have your data with columns like Sep-23, Oct-23, Nov-23, etc., and products in rows.

2. Power Query (Data Transformation):

   • Load your data into Power Query.
   • Follow these steps in Power Query to transpose your data:
     1. Select the first row (which contains the month names) in your data table.
     2. Right-click on the selected row and choose "Transpose."
     3. This will pivot your data so that the months become rows, and the products become columns.

3. Data Modeling in Power BI:

   • After transposing your data in Power Query, load it back into Power BI.
   • You will now have a table with columns for each product and rows for each month.

4. Measures:

   • Create measures for each product and month as needed. These measures will aggregate the data based on your business requirements. For example, if you want to sum the values for each product and month, you can create measures like this:

Use Dax:

Total_Sep_23 = SUM('YourTable'[Sep-23])
Total_Oct_23 = SUM('YourTable'[Oct-23])
-- Repeat for all months

1. Replace 'YourTable' with the actual name of your table.

2. Table Visual:

   • Create a table visual in Power BI.
   • In the Values field well of the table visual, place your measures (e.g., Total_Sep_23, Total_Oct_23, etc.).
   • In the Rows field well of the table visual, place the Date column.

With this approach, your table visual will dynamically show columns for each product and rows for each month, and the data will be aggregated according to the measures you created. As you add new data for future months, you won't need to manually adjust the columns in the visual; they will adapt automatically based on your transposed data structure.

If I answered your question, please mark my post as solution, Appreciate your Kudos.

[123abc]

To dynamically select columns in a table visual in Power BI based on your requirements, you can follow these steps:

1. Data Preparation: Ensure your data is in a format that's conducive to dynamic column selection. You should have columns for the month name and corresponding data. If your data currently looks like this:

Sep-23 Oct-23 Nov-23 Dec-23 Jan-24 Feb-24 Mar-24 Apr-24 May-24 Jun-24 Jul-24 Aug-24

1	100	120	130	140	150	160	170	180	190	200	210	220
2	90	110	120	130	140	150	160	170	180	190	200	210

You should transform it into:

ID Month Value

1	Sep-23	100
1	Oct-23	120
1	Nov-23	130
1	Dec-23	140
1	Jan-24	150
1	Feb-24	160
1	Mar-24	170
1	Apr-24	180
1	May-24	190
1	Jun-24	200
1	Jul-24	210
1	Aug-24	220
2	Sep-23	90
2	Oct-23	110
2	Nov-23	120
2	Dec-23	130
2	Jan-24	140
2	Feb-24	150
2	Mar-24	160
2	Apr-24	170
2	May-24	180
2	Jun-24	190
2	Jul-24	200
2	Aug-24	210

1. You can do this transformation in Power Query Editor.

2. Create a Date Parameter: Create a parameter that dynamically holds the latest month you want to include in your table visual. You can do this by going to the "Modeling" tab in Power BI Desktop and selecting "New Parameter." Set the data type to Date/Time and specify the allowed values based on your date columns.

3. Create a Measure: Create a measure that calculates the sum of the values for the selected months. The measure might look like this:

TotalValue =
VAR SelectedMonth = MAX('DateParameter'[Value])
RETURN
CALCULATE(SUM(YourTable[Value]), YourTable[Month] <= SelectedMonth)

1. Replace 'DateParameter' and 'YourTable' with the appropriate names from your model.

2. Create a Table Visual: Finally, create a table visual with the 'Month' column and the 'TotalValue' measure. You can use a slicer or a parameter to dynamically change the selected month, and the 'TotalValue' measure will adjust accordingly to sum the data for the selected months.

Now, when you change the parameter or slicer value to a specific month, the table visual will automatically adjust to display the data for that month and sum the values accordingly. Columns for future months will be automatically added as your data updates, and the old columns will be removed.