Forum Discussion

adamlang's avatar
adamlang
Icon for Helper III rankHelper III
2 years ago
Solved

Unique Person Count Overlapping Venn Diagram across services

Hi there,   At the early stage of thinking this through, so maybe I don't need a full solution but being pointed in the right direction.   One of my organisations top KPIs is the count of unique ...
Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago
    Unique Person Count = 
var c = [Combination]
var a = EVALUATEANDLOG(SUMMARIZE('Table',[URN],"Combination",var b = distinct('Table'[Service]) return CONCATENATEX(b,[Service],",",[Service],ASC)))
return countrows(filter(a,[Combination]=c))

    check Page2 of the attached.

     

adamlang's avatar
adamlang
Icon for Helper III rankHelper III
2 years ago

Thanks very much lbendlin 

 

That is the right answer, my manual count was wrong.

 

Just working through the detail of how to bring this solution into my main data model. For example wondering how the "Combinations" table itself was built. I looked at the query and could work out how the table developed, but the starting list of services - was that just imported as text, so when I build it i'll use the dimention table, and probably Summerize to start with a full list of the services?

 

Sorry for such a basic question.

 

Thanks,


Adam

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

The starting list is static in my example but can instead be derived from the raw data via List.Distinct([Column]). Let me know if you want to see an example.

 

Oh, and please ignore the EVALUATEANDLOG part, that's a leftover from debugging.