Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 7
Anonymous
Not applicable

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.

v-juanli-msft
Community Support
Community Support

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".

8.png

 

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.
9.png
 

 

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.

Anonymous
Not applicable

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

5.png

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.

Anonymous
Not applicable

 

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:

 

Untitled1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

Capture.PNG

 

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.

 

Anonymous
Not applicable

Hi,

 

Try this

 

table Name = SUMMARIZE('Calendar','Calendar'[ShrtMnth],"Name of new column",Name of measure,"Name of new colum2,Name of new of measure2 etc
 
Worked for me
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.