topic Re: Calculate error % of previous week in DAX Commands and Tips

Calculate error % of previous week

keivan — Wed, 05 Oct 2022 16:21:54 GMT

Hi all,

I have a dataset with several tables. The 2 i am taking in consideration are the date table and a table called fetch
fetch table containes all the information of downloads made for each connection. In this table I calculated a measure which analyse how many time the download got an error. I made this measure also in a % way

While date table is made with the following expression

date =

ADDCOLUMNS (

CALENDAR( DATE(2020,01,01), DATE(2025,12,31)),

"year", YEAR ( [Date] ),

"month name", FORMAT ( [Date], "mmmm" ),

"month number", MONTH ( [Date] ),

"week number", WEEKNUM([Date],2),

"day number", DAY( [Date])

)

I would like to report in a single table the error % on download got on a single day and the average error of the previous week. So that i may highlights if eeror rose recently

I try to do the following expression but I got an error:

True / False expression does not specify a column. Each True / False expression used as a table expression must refer to exactly one column.

Previous Week Authorize KO =

VAR CurrentWeek = MAXX('date', 'date'[week number])

VAR CurrentYear = MAXX('date','date'[year])

VAR MaxWeekNumber = CALCULATE('date', MAXX('date','date'[week number]), ALL('date'))

VAR AutKOWeek =

IF(

ISFILTERED('date'[week number]),

CALCULATE(

SUMX('fetches', [authorize_ko_technical%]

FILTER(

ALL('date'),

IF(

SELECTEDVALUE('date'[week number]) = 1,

'date'[year] = CurrentYear -1 && 'date'[week number] = MaxWeekNumber,

'date'[year] = CurrentYear && 'date'[week number] = CurrentWeek -1

)

[authorize_ko_technical%]

)

RETURN

IF(

ISBLANK([authorize_ko_technical%]),

BLANK(),

AutKOWeek

)

Can you please help me 😞

Re: Calculate error % of previous week

tamerj1 — Wed, 05 Oct 2022 17:02:03 GMT

Hi keivan

Please try

Previous Week Authorize KO = VAR CurrentWeek = MAXX ( 'date', 'date'[week number] ) VAR CurrentYear = MAXX ( 'date', 'date'[year] ) VAR MaxWeekNumber = CALCULATE ( 'date', MAXX ( 'date', 'date'[week number] ), ALL ( 'date' ) ) VAR AutKOWeek = IF ( ISFILTERED ( 'date'[week number] ), IF ( SELECTEDVALUE ( 'date'[week number] ) = 1, CALCULATE ( SUMX ( 'fetches', [authorize_ko_technical%] ), FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear - 1 && 'date'[week number] = MaxWeekNumber ) ), CALCULATE ( SUMX ( 'fetches', [authorize_ko_technical%] ), FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear && 'date'[week number] = CurrentWeek - 1 ) ) ), [authorize_ko_technical%] ) RETURN IF ( ISBLANK ( [authorize_ko_technical%] ), BLANK (), AutKOWeek )

Re: Calculate error % of previous week

keivan — Thu, 06 Oct 2022 13:21:22 GMT

Hi tamerj1

thanks for your help.

Unfortunately still getting the same error

True / False expression does not specify a column. Each True / False expression used as a table expression must refer to exactly one column.

Re: Calculate error % of previous week

tamerj1 — Thu, 06 Oct 2022 13:26:25 GMT

keivan
Would you please share a screenshot?

Re: Calculate error % of previous week

Anonymous — Thu, 13 Oct 2022 07:59:22 GMT

Hi keivan,

Any update on these? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Calculate error % of previous week

keivan — Wed, 26 Oct 2022 14:51:15 GMT

Sorry for the late reply.

Here the screenshot.
I'll try also to share some dummy data

Re: Calculate error % of previous week

keivan — Wed, 26 Oct 2022 15:25:09 GMT

Ok I'm not able to load a pbix file.

I try with several screenshot.
I reduce the model to 3 table. (a calendar, a client table, and a consent table)

In my business case, I want to analyze how many people on a web site provide the consent to access their personal data.

This is the consent table in which I have the client who perform the connection, the status and the date

My final table would be something like this

Everyday I want to monitor the conversion rate (consent ok/Consent Total) for each of my client. The tough point is that I want also to compare with previous week rolling average. So that i can easily spot witha delta if the performance of one of my client abruptly fall. I choose the previous week average beacuase it is a more stable data than the previous day.

I have calculated also some measure:

Consent Total = CALCULATE(SUM('consents'[consent_ok] ) + SUM('consents'[consent_new]) + SUM('consents'[consent_fail]))
and

conversion rate = CALCULATE(SUM('consents'[consent_ok])/[Consent Total])

now i would like to calculate consent_ok%

Re: Calculate error % of previous week

keivan — Wed, 26 Oct 2022 15:29:04 GMT

Hi Xiaoxin

I try to attach the pbix file but i got this error The file type (.pbix) is not supported.

Below I attached some screenshot and further detail.
Thanks,

Keivan

Re: Calculate error % of previous week

tamerj1 — Wed, 26 Oct 2022 16:41:36 GMT

keivan

Seems there is a typo mistake in line no. 7

Previous Week Authorize KO = VAR CurrentWeek = MAXX ( 'date', 'date'[week number] ) VAR CurrentYear = MAXX ( 'date', 'date'[year] ) VAR MaxWeekNumber = CALCULATE ( MAXX ( 'date', 'date'[week number] ), ALL ( 'date' ) ) VAR AutKOWeek = IF ( ISFILTERED ( 'date'[week number] ), IF ( SELECTEDVALUE ( 'date'[week number] ) = 1, CALCULATE ( SUMX ( 'fetches', [authorize_ko_technical%] ), FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear - 1 && 'date'[week number] = MaxWeekNumber ) ), CALCULATE ( SUMX ( 'fetches', [authorize_ko_technical%] ), FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear && 'date'[week number] = CurrentWeek - 1 ) ) ), [authorize_ko_technical%] ) RETURN IF ( ISBLANK ( [authorize_ko_technical%] ), BLANK (), AutKOWeek )

Re: Calculate error % of previous week

keivan — Fri, 28 Oct 2022 09:41:03 GMT

Thanks Actually there was a typo.

However unfortunately the result it seems uncorrect.

Here a screenshot

In the week from 10 to 16 october client A register a 9% error on the weekly base.
However when in the table I select any day of the week from 17 to 23 october (also if i select the all week) the result is an average error of 7%.

I check for several clients and it look that often the number do not match

Re: Calculate error % of previous week

tamerj1 — Fri, 28 Oct 2022 10:47:13 GMT

keivan

Please try

Previous Week Authorize KO = VAR CurrentWeek = MAXX ( 'date', 'date'[week number] ) VAR CurrentYear = MAXX ( 'date', 'date'[year] ) VAR MaxWeekNumber = CALCULATE ( MAXX ( 'date', 'date'[week number] ), ALLSELECTED ( 'date' ) ) VAR AutKOWeek = IF ( ISFILTERED ( 'date'[week number] ), IF ( SELECTEDVALUE ( 'date'[week number] ) = 1, CALCULATE ( SUMX ( 'fetches', [authorize_ko_technical%] ), FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear - 1 && 'date'[week number] = MaxWeekNumber ) ), CALCULATE ( SUMX ( 'fetches', [authorize_ko_technical%] ), FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear && 'date'[week number] = CurrentWeek - 1 ) ) ), [authorize_ko_technical%] ) RETURN IF ( ISBLANK ( [authorize_ko_technical%] ), BLANK (), AutKOWeek )

Re: Calculate error % of previous week

keivan — Fri, 28 Oct 2022 12:32:05 GMT

Unfortunately didn't change the result:

maybe it is due on how is calculated authorize_ko_technical measure?

Or maybe is the sumx function. I am actually looking for an average.
Thanks again

authorize_ko_technical%= 'fetches'[authorize_ko_technical]/('fetches'[authorize_ko_technical]+ 'fetches'[authorize_ok])

Re: Calculate error % of previous week

tamerj1 — Fri, 28 Oct 2022 12:39:56 GMT

keivan

What are you trying to achieve by this condition?

IF(

SELECTEDVALUE('date'[week number]) = 1,

Re: Calculate error % of previous week

keivan — Fri, 28 Oct 2022 14:28:03 GMT

Hi,

sorry to bother you again.
As you may guess I struggle a bit with dax formula more complicate than a Calculate 😞
Before opening a task on the community I tried to seek for other issues similar. So I tried with some copy paste. Indeed this was one of those. But Actually I don't fully understand the logic behind, as well the one in your formula 😞
I'm improving but some stuff is still out of my league

Re: Calculate error % of previous week

tamerj1 — Fri, 28 Oct 2022 20:28:18 GMT

keivan
I think now I understand the measure. However, it could have been much more simplier if you date tabe has a week sequential number that contimues to grow over years (does not restart every year). This can be achieved by calculated column using a dense rank over the [Year] * 100 + [week number]

However, based on the same method, please try

Previous Week Authorize KO = VAR CurrentWeek = MAXX ( 'date', 'date'[week number] ) VAR CurrentYear = MAXX ( 'date', 'date'[year] ) VAR MaxWeekNumber = CALCULATE ( MAXX ( 'date', 'date'[week number] ), ALL ( 'date' ), 'Date'[year] = CurrentYear - 1 ) VAR AutKOWeek = IF ( ISFILTERED ( 'date'[week number] ), IF ( SELECTEDVALUE ( 'date'[week number] ) = 1, CALCULATE ( [authorize_ko_technical%], FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear - 1 && 'date'[week number] = MaxWeekNumber ) ), CALCULATE ( [authorize_ko_technical%], FILTER ( ALL ( 'date' ), 'date'[year] = CurrentYear && 'date'[week number] = CurrentWeek - 1 ) ) ), [authorize_ko_technical%] ) RETURN IF ( NOT ISBLANK ( [authorize_ko_technical%] ), AutKOWeek )

Re: Calculate error % of previous week

keivan — Mon, 31 Oct 2022 13:26:55 GMT

Thank You so much 🙂