Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 have an application. Because of that, I have a table that has rows that are a result of a full outer join so that I always have the contact program data. Sample data would look like this:
AppId | App.ProgramId | App.ContactId | ContactProgram.ContactProgramId | ContactProgram.ProgramId | ContactProgram.ContactId | ContactProgram.ContactStatus |
App1 | Program1 | Contact1 | ContactProgram1 | Program1 | Contact1 | Active |
App2 | Program1 | Contact1 | ContactProgram1 | Program1 | Contact1 | Active |
App5 | Program1 | Contact2 | ContactProgram4 | Program1 | Contact2 | Active |
App6 | Program2 | Contact2 | ContactProgram4 | Program1 | Contact2 | Active |
App5 | Program1 | Contact2 | ContactProgram6 | Program2 | Contact2 | Interested |
App6 | Program2 | Contact2 | ContactProgram6 | Program2 | Contact2 | Interested |
App3 | Program2 | Contact3 | ContactProgram3 | Program2 | Contact3 | Active |
App4 | Program2 | Contact3 | ContactProgram3 | Program2 | Contact3 | Active |
ContactProgram7 | Program2 | Contact4 | Inactive | |||
ContactProgram10 | Program2 | Contact5 | Interested | |||
App1 | Program1 | Contact1 | ContactProgram12 | Program3 | Contact1 | Interested |
App2 | Program1 | Contact1 | ContactProgram12 | Program3 | Contact1 | Interested |
App5 | Program1 | Contact2 | ContactProgram9 | Program3 | Contact2 | Interested |
App6 | Program2 | Contact2 | ContactProgram9 | Program3 | Contact2 | Interested |
ContactProgram5 | Program3 | Contact4 | Active | |||
App5 | Program1 | Contact2 | ContactProgram8 | Program4 | Contact2 | Inactive |
App6 | Program2 | Contact2 | ContactProgram8 | Program4 | Contact2 | Inactive |
I want to create three measures on this table for use in a chart, that will give me the total intersted, total active, and total inactive, regardless of whether there is an application associated with the contact program record. So, for the above data, the resulting table/chart would look like this:
Program | Total Active | Total Inactive | Total Interested |
Program1 | 2 | ||
Program2 | 1 | 1 | 2 |
Program3 | 1 | 2 | |
Program4 | 1 |
I was able to get it to work by creating a table using the following DAX:
ContactPrograms = SUMMARIZE( 'Details', Details[ContactProgram.ContactId], Details[ContactProgram.ProgramId], Details[ContactProgram.ContactStatus] )
and then creating three measures (one each for active, inactive and interested) using DAX similar to this (all that changes is after the equals sign):
Total Active = CALCULATE( COUNT(ContactPrograms[ContactProgram.ContactId]), ContactPrograms[ContactProgram.ContactStatus] = "Active" )
Is there a better way to do this? It seems silly to use DAX to create a table to create a few measures. Can I create the measure in one statement that uses the summarize without creating a table that shows up in the model? Or is this the best approach? I've got a .pbix with the above data here.
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @jdballard30,
It seems that the solution from MFelix should be helpful.
If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
Hi @jdballard30,
You can use the summarize as part of a measure, so you don't need to create the additional table to your model just do the following measure to your data and use the ContactProgram.ContactStatus and ContactProgram.ProgramId columns on a matrix to make the split, and no need to make additional measures only one.
TotalprogamsBystatus = CALCULATE ( COUNTROWS ( SUMMARIZE ( 'Details'; Details[ContactProgram.ContactId]; Details[ContactProgram.ProgramId]; Details[ContactProgram.ContactStatus] ) ) )
As you can see below the green marked table is the one with the measure that is similar to your calculated table.
See attach the PBIX with the new measure and matrix visual.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSorry 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks. I'm still trying to learn some of the ins & outs of Power BI, and this was helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |