Forum Discussion

elinevans's avatar
elinevans
Icon for Helper I rankHelper I
5 years ago
Solved

Active Clients Multiple Services

  Here is an example of the type the data I'm using. Each client can have more than 1 service and I want to find clients active in a service within a time period (say between date x and date y...
DAX
Help
Help appreciated
  • Anonymous's avatar
    Anonymous
    5 years ago
    [# Active Clients] =
var MinReportingPeriod =
	MIN( Datetable[Date] ),
var MaxReportingPeriod =
	MAX( Datetable[Date] )
var Result =
	COUNTROWS(
		SUMMARIZE(
			CALCULATETABLE(
				ServiceInfo,
				KEEPFILTERS(
					ServiceInfo[Start Date] 
						<= MaxReportingPeriod
				),
				KEEPFILTERS(
					MinReportingPeriod
						<= ServiceInfo[End Date]
				)
			),
			ServiceInfo[Client Ref]
		)
	)
return
	Result
Anonymous's avatar
Anonymous
Not applicable
5 years ago
[# Active Clients] =
var MinReportingPeriod =
	MIN( Datetable[Date] ),
var MaxReportingPeriod =
	MAX( Datetable[Date] )
var Result =
	COUNTROWS(
		SUMMARIZE(
			CALCULATETABLE(
				ServiceInfo,
				KEEPFILTERS(
					ServiceInfo[Start Date] 
						<= MaxReportingPeriod
				),
				KEEPFILTERS(
					MinReportingPeriod
						<= ServiceInfo[End Date]
				)
			),
			ServiceInfo[Client Ref]
		)
	)
return
	Result
  • elinevans's avatar
    elinevans
    Icon for Helper I rankHelper I
    5 years ago

    HI Anonymous, using the above gave a count of the acive clients but what I want is a list of the client references of those clients who are active. Am I applying it wrong? 

     

    Thanks

    Elin

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      A measure can't return a table, only a scalar. When you say "a list of client references", what do you actually mean in this context? All you can return from a measure that imitates a list of values is a string with the values concatenated by using the CONCATENATEX function.

       

      You can take my code from above and instead of returning the count of active clients, you can use the function to return a list of references as explained before.

      • elinevans's avatar
        elinevans
        Icon for Helper I rankHelper I
        5 years ago

        Hi Anonymous 

         

        Sorry for not being very clear. I would like a filter to be applied to a table/matrix visual so when i put in client ref as one of the values it will only show me client references of client who are active at any point during the repoting period.

         

        Thanks

        Elin