cancel
Showing results for
Did you mean:
Highlighted
Helper I

## How to create sentence from report table data?

Hi,

I have such data:

I've created 2 report tables for this and last week data (only LastWeek and ThisWeek are measures):

I would like to ask: is it possible to create this kind of sentence, using report tables' above?
Failure 4.32% -> 0.74%; Untested: 3.87% -> 1.19%. Failed Groups 19 -> 5

Thanks,
Agnė
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Hi, @Agne

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

``Calendar = CALENDARAUTO()``

There is no relationship between two tables. You may create calculated columns and a measure as below.

Calculated column:

``````YearWeekCal = YEAR('Calendar'[Date])*100+WEEKNUM('Calendar'[Date])

YearWeek = YEAR('Table'[Date])*100+WEEKNUM('Table'[Date])

WeekDesc =
var _yw = YEAR(TODAY())*100+WEEKNUM(TODAY())
var _lastyw =
CALCULATE(
MAX('Calendar'[YearWeekCal]),
FILTER(
ALL('Calendar'),
[YearWeekCal]<_yw
)
)
return
IF(
[YearWeek]=_yw,
"This Week",
IF(
[YearWeek]=_lastyw,
"Last Week"
)
)``````

Measure:

``````Result =
var lastfailrate= (LOOKUPVALUE('Table'[Failed%],'Table'[WeekDesc],"Last Week")*100)&"%"
var failrate = (LOOKUPVALUE('Table'[Failed%],'Table'[WeekDesc],"This Week")*100)&"%"
var lastuntestrate = (LOOKUPVALUE('Table'[Untested%],'Table'[WeekDesc],"Last Week")*100)&"%"
var untestrate = (LOOKUPVALUE('Table'[Untested%],'Table'[WeekDesc],"This Week")*100)&"%"
var lastfailgroups = LOOKUPVALUE('Table'[Failed Groups],'Table'[WeekDesc],"Last Week")
var failgroups = LOOKUPVALUE('Table'[Failed Groups],'Table'[WeekDesc],"This Week")
return
"Failure:"&lastfailrate&"->"&failrate&";"&
"Untested:"&lastuntestrate&"->"&untestrate&";"&
"Failed Groups:"&lastfailgroups&"->"&failgroups``````

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Highlighted
Super User IV

@Agne Yes, a little hard to follow but should be along the lines of something like:

``````Measure =
"Failure: " & CALCULATE([Failed %],<last week>) & "->" & CALCULATE([Failed %],<this week>) & "; Untested: "...``````

For a good example of doing something like this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Most-Amazing-Mind-Blowing-Dynamic-Slicer...

Otherwise, @ me in replies and I would need more details. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Super User IV

@Agne , Not very clear. If you need this week vs last week refer to my blog

It uses Date table and week rank

Proud to be a Super User!

Highlighted
Helper I

Your measure is very likely what I need, but it is not possible to use [Failed%] and [Untested%] columns, because they are calculated columns but not measures. I noticed, that I could use only measures in your suggested formula.

Highlighted
Super User IV

@Agne To use columns in a measure formula, you need to wrap the column in an aggregation like MAX, MIN, SUM, AVERAGE, etc. I often use MAX or MIN if I am confident that there is only a single value for the column within the current context.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Community Support

Hi, @Agne

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

``Calendar = CALENDARAUTO()``

There is no relationship between two tables. You may create calculated columns and a measure as below.

Calculated column:

``````YearWeekCal = YEAR('Calendar'[Date])*100+WEEKNUM('Calendar'[Date])

YearWeek = YEAR('Table'[Date])*100+WEEKNUM('Table'[Date])

WeekDesc =
var _yw = YEAR(TODAY())*100+WEEKNUM(TODAY())
var _lastyw =
CALCULATE(
MAX('Calendar'[YearWeekCal]),
FILTER(
ALL('Calendar'),
[YearWeekCal]<_yw
)
)
return
IF(
[YearWeek]=_yw,
"This Week",
IF(
[YearWeek]=_lastyw,
"Last Week"
)
)``````

Measure:

``````Result =
var lastfailrate= (LOOKUPVALUE('Table'[Failed%],'Table'[WeekDesc],"Last Week")*100)&"%"
var failrate = (LOOKUPVALUE('Table'[Failed%],'Table'[WeekDesc],"This Week")*100)&"%"
var lastuntestrate = (LOOKUPVALUE('Table'[Untested%],'Table'[WeekDesc],"Last Week")*100)&"%"
var untestrate = (LOOKUPVALUE('Table'[Untested%],'Table'[WeekDesc],"This Week")*100)&"%"
var lastfailgroups = LOOKUPVALUE('Table'[Failed Groups],'Table'[WeekDesc],"Last Week")
var failgroups = LOOKUPVALUE('Table'[Failed Groups],'Table'[WeekDesc],"This Week")
return
"Failure:"&lastfailrate&"->"&failrate&";"&
"Untested:"&lastuntestrate&"->"&untestrate&";"&
"Failed Groups:"&lastfailgroups&"->"&failgroups``````

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors