DAX Measure for Active Subscribers not plotting in Excel

MEverts — Mon, 11 May 2020 20:54:35 GMT

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

Re: DAX Measure for Active Subscribers not plotting in Excel

Mariusz — Wed, 13 May 2020 07:57:45 GMT

Hi MEverts

You can use

VAR _CurrDate = MAX( 'Calendar'[Date] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: DAX Measure for Active Subscribers not plotting in Excel

MEverts — Wed, 13 May 2020 09:51:21 GMT

Dear Mariusz,

Thank you so much!! It works:-)

Best regards,

Martijn

Re: DAX Measure for Active Subscribers not plotting in Excel

Mariusz — Wed, 13 May 2020 12:51:49 GMT

Hi MEverts

No problem, happy to help!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

topic Re: DAX Measure for Active Subscribers not plotting in Excel in DAX Commands and Tips

DAX Measure for Active Subscribers not plotting in Excel

Re: DAX Measure for Active Subscribers not plotting in Excel

Re: DAX Measure for Active Subscribers not plotting in Excel

Re: DAX Measure for Active Subscribers not plotting in Excel