Forum Discussion
Calculated Columns or Measure to get the output
Hello Friends,
Please help me to write either DAXm Measure or calculated column in this scenario.
I have provided sample input data and expected output in table. We have one master data & reports data. Both tables linked n PRJID. How can add below columns to the master table using the reports table data.
Required Reports : Count of reports when date value is present in any columns (G0 to G5)
Available Reports : Count of reports available in ReportsTable for the project . Eg: Prj1 we suppose to have 4 entries but only 3 isavailable. G4 is missing
Gates Missing: Prj1 we suppose to have 4 entries but only 3 is available. Missing report is G4
Document Link Missing : Corresponding report entry is present in report table but Doc Link is empty
| Output table | ||||||||||
| PrjID | PrjName | Gate | G0 Date | G2 Date | G4 Date | G5 Date | Required Reports | Available Reports | Gates Missing | Document Link Missing |
| P1 | Prj1 | G5 | 2-Feb-24 | 2-Jun-24 | 2-Oct-24 | 2-Dec-24 | 4 | 3 | G4 | G4 |
| P2 | Prj2 | G4 | 2-Feb-24 | 2-Dec-24 | 2 | 2 | G4 | |||
| P3 | Prj3 | G5 | 2-Feb-24 | 2-Dec-24 | 2-Dec-24 | 2-Feb-25 | 3 | 0 | G0,G4,G5 | G0,G4,G5 |
| P4 | Prj4 | G5 | 2-Feb-24 | 2-Feb-25 | 2 | 2 | G0 | |||
| P5 | Prj5 | G2 | 2-Feb-24 | 2-Jun-24 | 2-Mar-26 | 2-Dec-26 | 4 | 1 | 1 |
| Master Table | ||||||
| PrjID | PrjName | Gate | G0 Date | G2 Date | G4 Date | G5 Date |
| P1 | Prj1 | G5 | 2-Feb-24 | 2-Jun-24 | 2-Oct-24 | 2-Dec-24 |
| P2 | Prj2 | G4 | 2-Feb-24 | 2-Dec-24 | ||
| P3 | Prj3 | G5 | 2-Feb-24 | 2-Dec-
24 | 2-Dec-24 | 2-Feb-25 |
| P4 | Prj4 | G5 | 2-Feb-24 | 2-Feb-25 | ||
| P5 | Prj5 | G3 | 2-Feb-24 | 2-Jun-24 | 2-Apr-26 | 2-Dec-26 |
| ReportsTable | |||
| PrjID | Gate | Last Gate Date | Doc Link |
| P1 | G0 | 2-Feb-24 | link |
| P1 | G2 | 2-Jun-24 | link |
| P1 | G5 | 2-Dec-24 | link |
| P2 | G0 | 2-Feb-24 | link |
| P2 | G4 | 2-Dec-24 | |
| P4 | G0 | 2-Feb-05 | |
| P5 | G2 | 2-Jun-24 | link |
| P4 | G5 | 2-Feb-05 | link |
Hi manojk_pbi ,
Thank you for your response. I have adjusted the measure accordingly and hope it now meets your requirements. Please review the attached PBIX file.
6 Replies
Hi manojk_pbi ,
Thank you for reaching out to Microsoft Community.
Please review the attached PBIX file, which has been configured to match your stated requirements.
Best Regards
Chaithra E.v-echaithra , Thanks for creating a sample and configuring the required measures. Few are not working as expected.
If I need to present Required Reports, Available Reports count in KPI visual, numbers are not correct. Even the measure is not working properly when we have total in the table.
Actually available reports are 8 but table shows 4. It is taking distinct count instead it shuld add up all the values.
How can we modify to get the sum of all required and available reports
Hi manojk_pbi ,
Thank you for your response. I have adjusted the measure accordingly and hope it now meets your requirements. Please review the attached PBIX file.Hi manojk_pbi ,
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Thank you.Thanks v-echaithra , your updated solution helped to address the issue.
Hello v-echaithra ,
I have one more requirement with the similar usecase posted here . Could you pls have look and guide me
