Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear,
I'm looking to plot the number of Open and Answered data-entry queries in Excel using a dax patern for active subscribers/tickets.
My Source data looks like this, together with a disconected Calendar-table:
ID | Site | #Subject ID | Type | Current Status | Start_date_status | End_date_status |
1171 | 102 | 102001 | System Query | Open State | 11/23/2017 14:35 | 11/23/2017 14:37 |
1172 | 102 | 102001 | System Query | Open State | 11/23/2017 14:40 | 11/27/2017 19:49 |
1173 | 102 | 102001 | Site Query from PM | Open State | 1/4/2018 22:35 | 1/10/2018 15:05 |
1173 | 102 | 102001 | Site Query from PM | Answered State | 1/10/2018 15:05 | 4/16/2018 20:39 |
1174 | 102 | 102001 | Site Query from PM | Open State | 1/4/2018 22:37 | 1/10/2018 15:02 |
1174 | 102 | 102001 | Site Query from PM | Answered State | 1/10/2018 15:02 | 4/16/2018 20:40 |
1175 | 102 | 102001 | Site Query from PM | Open State | 1/4/2018 22:38 | 10/17/2018 16:30 |
1175 | 102 | 102001 | Site Query from PM | Answered State | 10/17/2018 16:30 | 10/18/2018 15:16 |
1176 | 102 | 102001 | Site Query from PM | Open State | 1/4/2018 22:39 | 1/10/2018 16:17 |
1176 | 102 | 102001 | Site Query from PM | Answered State | 1/10/2018 16:17 | 7/20/2018 16:01 |
I have got it working in Power BI by amending the helpful patern at the Radacad website, which is great:
Active Subscribers =
VAR _CurrDate =
SELECTEDVALUE( 'Calendar'[Date] )
VAR _FirstStartDate =
MIN ( 'STATIC_Query Metrics_Final'[Start_date_status] )
VAR _Queries =
FILTER (
'STATIC_Query Metrics_Final',
'STATIC_Query Metrics_Final'[End_date_status] >= _CurrDate
&& 'STATIC_Query Metrics_Final'[Start_date_status] < - _CurrDate
)
VAR _countRows =
COUNTROWS ( _Queries )
RETURN
IF (
_CurrDate <= TODAY ()
&& _CurrDate >= _FirstStartDate,
IF ( ISBLANK ( _countRows ), 0, _countRows )
)
However, I would like to have a similar plot in an Excell dashboard.
As the SELECTEDVALUES command is not available in Excel 365, I instead use IF(HASONEVALUE( 'Calendar'[Date]),VALUES( 'Calendar'[Date]), as seen below for the first Variable _CurrDate:
Active Subscribers :=
VAR _CurrDate =
IF ( HASONEVALUE ( 'Calendar'[Date] ), VALUES ( 'Calendar'[Date] ) )
VAR _FirstStartDate =
MIN ( 'STATIC_Query Metrics_Final'[Start_date_status] )
VAR _Queries =
FILTER (
'STATIC_Query Metrics_Final',
'STATIC_Query Metrics_Final'[End_date_status] >= _CurrDate
&& 'STATIC_Query Metrics_Final'[Start_date_status] < - _CurrDate
)
VAR _countRows =
COUNTROWS ( _Queries )
RETURN
IF (
_CurrDate <= TODAY ()
&& _CurrDate >= _FirstStartDate,
IF ( ISBLANK ( _countRows ), 0, _countRows )
)
Perhaps my understanding of the Excel alternative for SELECTEDVALUE incorrect, but unfortunately when create a area plot in the exact same way as in Power BI, there is no reasonable output, just a blank graph with an y-axis from 0 to 1:
I must be doing something wrong; any help or insight you could give me is much appreciated.
Best regards,
Martijn
Solved! Go to Solution.
Dear Mariusz,
Thank you so much!! It works:-)
Best regards,
Martijn
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |