Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |