Creating a new table with measures in rows

[SajjadMousavi]

Hi

I have a few measures, say M1, M2, M3, which depend on year and month (I already have a date dimension table). Because most of visuals for BSC dashboard and reports, and most of KPI visuals which are suitable for my case, expect measures names to be values in table columns (not single measures), I need to create table like this (from the above measures):

Year	Month	Measure name	Measure value
2020	1	Measure 1	12
2020	1	Measure 2	56.6
2020	1	Measure 3	1.3
...	...	...	...

And so on. Now my question is: How can I create a table in the above format from measures which are already mentioned? I know that I MAY be able to do this using Power Query, but there are two problems: First, writing complex calculations for goals, targets and indices in strategy area using Power Query is not as simple as DAX; And second, when I write measures using DAX, I can't use them back in Power Query to create such a table (provided creating such a table in Power Query is possible at all, which I'm not sure about).

Thanks in advance.

[tamerj1 · Accepted solution]

Hi @SajjadMousavi 
You can start with creating a crossjoin table between Measure Names and the values of Year-Month column of the date table. Something like
Filter Table =
CROSSJOIN ( VALUES ( 'Date'[YearMonth] ), { "Measure 1", "Measure 2", "Measure 3" } )
Then create a relationship between the Year-Month columns in the two tables (many to many). The values column that contains the names of the measures can then be used in the table visual without generating error message about the relationship.

Next is simple. Create one measure wich contains switch statement. Something like
Measure =

VAR SelectedMeasure = SELECTEDVALUE ( 'Filter Table'[Value] )
RETURN
    SWITCH (
        TRUE ( ),
        SelectedMeasure = "Measure 1", [Measure 1],
        SelectedMeasure = "Measure 2", [Measure 2],

        SelectedMeasure = "Measure 3", [Measure 3]
     )

[SajjadMousavi]

Thanks a lot for your solution. It worked flawlessly! It just needed a small correction in my case (and I don't know why!):

VAR SelMeasure = CALCULATE(MAX(MeasureTable[MeasureName]), MeasureTable[MeasureName] = EARLIER(MeasureTable[MeasureName]))

The above is because SELECTEDVALUE always returns blank (It's the first time I faced such an issue). The above is a trick I have used before when I needed EARLIER without actually using CALCULATE.