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

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.