Forum Discussion
Dynamically changing columns
hi Anonymous ,
Your data is not in the best format and it would be real pain to use the same table structure for data model. I would:
- select partner and then unpivot all other columns
-
- extract the year from the resulting attribute column
-
- load the query
- create a calculated column to get the max year as per the table and compare each row of year to the max year to return only those that are >=MaxYear-2 and use this calculated column as the filter.
-
Please see attached sample pbix for reference
Hi Anonymous -To select the columns based on the current year dynamically. Below dax function works,
Please try below measure
CurrentYear = YEAR(TODAY())
Current Year:
Table_CurrentYear =
IF(
MAX('Table'[Year]) = [CurrentYear],
'Table'[Table Rating],
BLANK()
)
Previous Year
Table_PreviousYear =
IF(
MAX('Table'[Year]) = [CurrentYear] - 1,
'Table'[Table Rating],
BLANK()
)
2 YearBefore
Table_YearBefore =
IF(
MAX('Table'[Year]) = [CurrentYear] - 2,
'Table'[Table Rating],
BLANK()
)
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!