Dynamic Period display into the column section of the matrix visual with default periods for column

[V_Ramugade]

Hi All,

I have a yearcodeperiodlist dataset & financialtransactiondetails dataset. There is a PeriodNew, YearCode & Period text field into YearCodePerodList dataset. PeriodNew field contains values like 00, 97, 98, 99, None & Reg. YearCode field contains values like 2024/25, 2023/24, 2022/23 and Period field contains values like 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 97, 98, 99, reg.
Finacialtransactiondetails have Location & Nominal text fields and Value numeric field.

Now I want Yearcode and PeriodNew field into the slicer and Location & Nominal field into the row section of the matrix visual and Period field into the column section of the matrix and values from the financial transaction details table should be into the values section of the matrix visual.

Now, I want
CASE 1 - if I select the 00 from the periodnew slicer then matrix visual should display location, nominal into the row section and for column section my period field should display 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12.

CASE 2 - if I select the 97 from the periodnew slicer then matrix visual should display location, nominal into the row section and for column section my period field should display 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 97.
CASE 3 - if I select the 98 from the periodnew slicer then matrix visual should display location, nominal into the row section and for column section my period field should display 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 98.

CASE 4 - if I select the 99 from the periodnew slicer then matrix visual should display location, nominal into the row section and for column section my period field should display 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 99.

CASE 5 - if I select the None from the periodnew slicer then matrix visual should display location, nominal into the row section and for column section my period field should display 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12.

CASE 6 - if I select the reg from the periodnew slicer then matrix visual should display location, nominal into the row section and for column section my period field should display Reg, 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12. 

[bhanu_gautam]

@V_Ramugade 

Add a calculated column to your YearCodePeriodList dataset to handle the dynamic period display based on the PeriodNew selection.
PeriodDisplay =
SWITCH(
TRUE(),
'YearCodePeriodList'[PeriodNew] = "00", "00",
'YearCodePeriodList'[PeriodNew] = "97", "97",
'YearCodePeriodList'[PeriodNew] = "98", "98",
'YearCodePeriodList'[PeriodNew] = "99", "99",
'YearCodePeriodList'[PeriodNew] = "None", "None",
'YearCodePeriodList'[PeriodNew] = "Reg", "Reg",

Create a measure that will dynamically generate the period columns based on the PeriodNew selection.
DynamicPeriodColumns =
VAR SelectedPeriodNew = SELECTEDVALUE('YearCodePeriodList'[PeriodNew])
RETURN
SWITCH(
TRUE(),
SelectedPeriodNew = "00", "00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12",
SelectedPeriodNew = "97", "01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 97",
SelectedPeriodNew = "98", "01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 98",
SelectedPeriodNew = "99", "01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 99",

Create a measure to display the values from the FinancialTransactionDetails table.
TransactionValue = SUM('FinancialTransactionDetails'[Value])

Add the Location and Nominal fields from the FinancialTransactionDetails table to the Rows section of the matrix visual.
Add the Period field from the YearCodePeriodList table to the Columns section of the matrix visual.
Add the TransactionValue measure to the Values section of the matrix visual.

Use conditional formatting to dynamically show/hide columns based on the DynamicPeriodColumns measure.
Unfortunately, Power BI does not directly support dynamic column visibility based on slicer selection. However, you can use the DynamicPeriodColumns measure to create a visual filter or use bookmarks and selection panes to simulate this behavior.