Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct Count

Hi,   I couldn't see an answer in the forum... so here it goes. Thanks in advance.   I have a list of projects (ProjectID) in my table Projects-all. Each of them has different organisations (Orga...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi, so Total and Average are all not correct? Try this:

     

    Number Coll Org=

    COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="COLLAB_ORG"),'projects-all'[Organisationid],'projects-all'[projectid]))

     
    Number PP=
    COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="PP_ORG"),'projects-all'[Organisationid],'projects-all'[projectid]))
     
    Av COLLAB_ORG =
    VAR NumProject=COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="COLLAB_ORG"),'projects-all'[projectid]))
    RETURN
    DIVIDE([Number Coll Org],NumProject)
     
    Av PP =
    VAR NumProject=COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="PP_ORG"),'projects-all'[projectid]))
    RETURN
    DIVIDE([Number PP],NumProject)
     
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi all,

 

I hope you're feeling fine.

 

I could manage to generate the average for a lot of dimensions. But, I couldn't find any DAX formula that couldn't help me with the challenge I have. 

 

Here's a link to an excel file of projects I want to analyse. As you can see, each has a number of research topics and research subjects. Each project could address the same topics though. I have a massive list of Research Topics and Subjects that any project (project table) could address. So now, my aim is to provide a list of Research Topics and Research Subjects that filtered projects don't address. Each table "Resarch Topic" and "Research Subject" provide a column for the name and another for the ID (provided in the excel file). Ideally, there should be a DAX formula that will compare the Research Topic/Subject ID from each project (first excel tab) against the list of IDs I have in tables "Research Topic" and "Research Subject", and return a list of Research Topics and Subjects that projects don't cover. Then, the PBI desktop should show a table with these results.

 

I hope the excel file works fine. The pbi file is 200mb and I don't know how to cut it down to only show what I just explained. I've also attached the data model. I know it might not look "efficient", but this is what I come up to avoid manytomany relatioships if I used "projects" table.

 

Thanks again so much for your help.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    sorry...the internet issue...I mean could you please show what you want? Use Excel to put some ideal result

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Hi Vera, your message is in blank.

      Thanks