Forum Discussion
Get measures in data table
So I have a number of measures calculated from source data in different tables. All of these tables contain date fields, which are all linked to a central Calendar table.
I have made a matrix in my report which contains all my measures and results for months from the Calendar table:
| Apr-18 | May-18 | Jun-18 | Jul-18 |
Staff attrition | 11% | 12% | 11% | 11% |
Percentage of widget POs processed in ten days or under | 78% | 79% | 82% | 86% |
Percentage of approved FTE filled | 100% | 100% | 100% | 100% |
Does anyone know a straightforward way to write DAX code to bring this measure data into a data table, i.e. a table that can be manipulated/filtered/referred to in the Data View?
e.g., something like this:
Month (from Calendar) | Measure Name | Value |
Apr-18 | Staff attrition | 11% |
Apr-18 | Percentage of widget POs processed in ten days or under | 78% |
Apr-18 | Percentage of approved FTE filled | 100% |
May-18 | Staff attrition | 12% |
May-18 | Percentage of widget POs processed in ten days or under | 79% |
etc.
Any ideas much appreciated.
7 Replies
Hi Anonymous
Here is a workaround.
Add "month-year" column in the "Row" of matrix, add measures in the "Value" of matrix,
Turn on "show on rows" under "Value" formatting, turn off "Stepped layout" off under "Row headers".
In my test, "month-year" column is from Calendar table
calendar = ADDCOLUMNS(CALENDARAUTO(),"month-year",FORMAT([Date],"mmm-yy"))
You could enter words in text box and add it on the matrix column headers.Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi v-juanli-msft ,
Thanks for your reply. Unfortunately, this is not what I was asking for. I already have what I want in the matrix. I want some way of creating what I have outlined above as a table in the Data View, so I can then write measures, calculated columns that act on it there.
Hi Anonymous
You could create a new table
Table = UNION ( SELECTCOLUMNS ( ADDCOLUMNS ( widget, "year/month", FORMAT ( [Date], "mmm-yy" ) ), "month-year", [year/month], "measure name", "Percentage of widget", "value", [Percentage of widget] ), SELECTCOLUMNS ( ADDCOLUMNS ( staff, "year/month", FORMAT ( [Date], "mmm-yy" ) ), "month-year", [year/month], "measure name", "Staff attrition", "value", [Staff attrition] ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
