Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
True / False expression does not specify a column. Each True / False expression used as a table expression must refer to exactly one column.
Can you please help me 😞
Solved! Go to Solution.
@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 )
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
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
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.
Sorry for the late reply.
Here the screenshot.
I'll try also to share some dummy data
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 )
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
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 )
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
What are you trying to achieve by this condition?
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
@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 )
Thank You so much 🙂
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:
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 )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |