Forum Discussion

SajjadMousavi's avatar
SajjadMousavi
Icon for Helper II rankHelper II
4 years ago
Solved

Creating a new table with measures in rows

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):

 

YearMonthMeasure nameMeasure value
20201Measure 112
20201Measure 256.6
20201Measure 31.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.

DAX
Measures
Table
  • tamerj1's avatar
    tamerj1
    4 years ago

    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]
         )

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      SajjadMousavi
      Icon for Helper II rankHelper II
      4 years ago

      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.