Power Pivot Measure and Time Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2261181%22%20slang%3D%22en-US%22%3EPower%20Pivot%20Measure%20and%20Time%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2261181%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20size%3D%224%22%3EHi%20everyone.%20Your%20advice%20is%20much%20appreciated...%20hope%20the%20situtation%20is%20clear%20(apologies%20that%20I%20cannot%20upload%20files%20from%20work%20environment).%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EI%20have%20set%20up%20the%20following%20measure%20to%20count%20the%20number%20of%20distinct%20%22Gold%22%20tiered%20clients%20that%20different%20teams%20have%20provided%20consultation%20services%20to.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3Dcalculate(DISTINCTCOUNT(ServiceDetail%5BClient%20Name%5D)%2C%20ServiceDetail%5BActivity%20Type%5D%3D%E2%80%9CConsultation%E2%80%9D%2C%26nbsp%3B%3CSPAN%3EClientInfo%5BTier%5D%3D%E2%80%9CGold%E2%80%9D%3C%2FSPAN%3E)%3C%2FFONT%3E%3C%2FP%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3EBuilding%20on%20this%2C%20I%20am%20trying%20to%20reference%20two%20other%20fields%3A%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3EServiceDetail%5BActivity%20ID%5D%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3EServiceDetail%5BActivity%20Date%5D%26nbsp%3B%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3Ein%20order%20to%20achieve%20the%20following%3A%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3E1.%20I%20would%20like%20to%20count%20the%20number%20of%20Gold%20tiered%20clients%20that%20receive%20consultations%20on%20a%20regular%20basis.%20For%20example%2C%20at%20least%205%20times%20in%20the%20past%20year.%26nbsp%3B%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3E2.%20I%20would%20like%20to%20exclude%20duplicates%20(based%20on%20Activity%20ID).%20Sometimes%20the%20same%20activity%20is%20replicated%20within%20the%20ServiceDetail%20table%20if%20multiple%20team%20members%20are%20involved.%26nbsp%3B%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%20size%3D%224%22%3EI'm%20struggling%20to%20figure%20this%20out%2C%20so%20would%20be%20grateful%20for%20pointers!%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2261181%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2262744%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20Measure%20and%20Time%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2262744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949839%22%20target%3D%22_blank%22%3E%40ahhk2000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20hard%20to%20say%20exactly.%20If%20I%20had%20your%20situation%20in%20front%20of%20me%2C%20I%20think%20I'd%20be%20trying%20some%20combination%20of%20the%20new%20functions%20(only%20available%20in%20the%20newest%20versions%20of%20Excel)%20FILTER%2C%20SORT%2C%20UNIQUE...%20combining%20them%20with%20COUNT%20or%20one%20of%20the%20other%20older%20functions%20that%20will%20count%20rows%20in%20a%20given%20population.%20FILTER%20can%20go%20through%20a%20database%20and%20extract%20(for%20COUNTing)%20rows%20that%20meet%20your%20specified%20criteria%20(and%20the%20criteria%20can%20be%20quite%20complex).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20video%20that%20I%20have%20found%20very%20helpful%20in%20determining%20how%20those%20functions%20work.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20may%20be%20that%20some%20other%20folks%20here%20will%20have%20other%20answers%3B%20if%20nothing%20comes%20forth%20that%20is%20satisfactory%2C%20perhaps%20you%20could%20post%20a%20facsimile%20of%20your%20sheet%20(actual%20Excel%20please%3B%20not%20just%20an%20image)%20just%20with%20all%20confidential%20info%20rendered%20anonymous.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone. Your advice is much appreciated... hope the situtation is clear (apologies that I cannot upload files from work environment). 

 

I have set up the following measure to count the number of distinct "Gold" tiered clients that different teams have provided consultation services to. 

 

=calculate(DISTINCTCOUNT(ServiceDetail[Client Name]), ServiceDetail[Activity Type]=“Consultation”, ClientInfo[Tier]=“Gold”)

 
Building on this, I am trying to reference two other fields:
ServiceDetail[Activity ID]
ServiceDetail[Activity Date] 
in order to achieve the following:
 
1. I would like to count the number of Gold tiered clients that receive consultations on a regular basis. For example, at least 5 times in the past year. 
 
2. I would like to exclude duplicates (based on Activity ID). Sometimes the same activity is replicated within the ServiceDetail table if multiple team members are involved. 
 
I'm struggling to figure this out, so would be grateful for pointers!
 
1 Reply

@ahhk2000 

 

It's hard to say exactly. If I had your situation in front of me, I think I'd be trying some combination of the new functions (only available in the newest versions of Excel) FILTER, SORT, UNIQUE... combining them with COUNT or one of the other older functions that will count rows in a given population. FILTER can go through a database and extract (for COUNTing) rows that meet your specified criteria (and the criteria can be quite complex).

 

Here's a video that I have found very helpful in determining how those functions work.

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

It may be that some other folks here will have other answers; if nothing comes forth that is satisfactory, perhaps you could post a facsimile of your sheet (actual Excel please; not just an image) just with all confidential info rendered anonymous.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...