Forum Discussion

jdballard30's avatar
jdballard30
Helper II
7 years ago
Solved

DistinctCount with summarize

My data involves Applications, Contacts and Programs. Applications have contacts, and can be associated with a program. However, it's possible that a contact can be associated with a program, but not...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi jdballard30,

     

    You had the calculation correct. You just need to add the Program.ProgramStartDate on your columns drill down on the last level of the matrix and remove the subtotals on start date level and it will work.

     

    Check the PBIX attach.

     

    Regards,

    MFelix

jdballard30's avatar
jdballard30
Helper II
7 years ago

Sorry that I haven't replied sooner; other things took priority and I'm just now getting back to this.

 

So that makes sense, but I (unintentionally) left out one item that might change things. Say that on the Program table there is another field ProgramStartDate, and I want that as a column in the chart, like this:

 

 

I tried with your solution to add it as a row, column, and value, and none of those worked. Would your solution work if I wanted to include the above? I also have another measure (which is calculating correctly but isn't in the data model that I outlined, which is a simplified version of the real thing) that I want to put on the chart. When I add that additional measure, which isn't based on the total by rows, the data doesn't appear correctly.

MFelix's avatar
MFelix
Super User
7 years ago

Hi jdballard30,

 

Without the full information is difficult to give the correct result at first time, in theory if you add the Start date to the summarize formula on your measure, and then add the date in the matrix visual should work fine.

 

If you can share a sample with the Start date i can check it.

 

Regards,

MFelix

  • jdballard30's avatar
    jdballard30
    Helper II
    7 years ago

    Thanks for the quick response and the help! And my apologies for not including the start date in my original question. Your answer definitely answered the original question.

     

    I've updated the model. The Program Start Date lives on the Program query, but I also have merged it into the Details query. I tried adding it in various ways, but couldn't seem to make it work (which is probably my lack of knowledge.) Updated pbix file is here.

     

    • MFelix's avatar
      MFelix
      Super User
      7 years ago

      Hi jdballard30,

       

      You had the calculation correct. You just need to add the Program.ProgramStartDate on your columns drill down on the last level of the matrix and remove the subtotals on start date level and it will work.

       

      Check the PBIX attach.

       

      Regards,

      MFelix

      • jdballard30's avatar
        jdballard30
        Helper II
        7 years ago

        Thanks. I'm still trying to learn some of the ins & outs of Power BI, and this was helpful.