How to create sentence from report table data?

[Agne]

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ė

[v-alq-msft · Accepted solution]

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.

[amitchandak]

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

It uses Date table and week rank 

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[Greg_Deckler]

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

[Agne]

@Greg_Deckler , thank you for your answer. 

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. 

[Greg_Deckler]

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