Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
I have the exact same issue,
I know this is not a perfect solution but I currently Export a visual to Excel and Import it again in PBI to get the data I want as a datatable.
Looking forward to see another solution.
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
Maggie
Community 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
Maggie
Community 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. This is the kind of result I want, but I don't currently have tables with the measure results precalculated in them like the "widget" and "staff" tables that you have. Instead, I have tables with raw data, and measures that calculate from that raw data, as below:
Measure formula is:
Median elapsed working days to process any product (food, hygiene, etc.) licence or registration = MEDIAN('5 LRC_tblLicencesIssued'[Working Days])
"Licence Issued Date" is linked to "'Calendar'[Date]" in my calendar table, and using the "'Calendar'[ShrtMnth]" field from that table I am able to generate the matrix:
Given that my measures/tables are structured like this, is there a way to produce a result like you had in your post? Thanks again for your help so far.
Hi,
Try this
Hi @Anonymous ,
Thanks for your help. The issue is that I need the names of the different measures to all be in the same column.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |