Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure for DOES NOT EXIST

I need to write a measure that where EpicKey doesn't not exist in another table.

 

Epic table (Epickey, ProjectKey)
Issue table (IssueKey, EpicKey, ProjectKey)

 

The measure needs to give me a distinct count of ProjectKey where Epic.Epickey does not exist in Issue.Epickey

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I think I solved it with EXCEPT.

    Count of Projects = CALCULATE(
CALCULATE(
DISTINCTCOUNT(JiraEpic[ProjectKey]),
EXCEPT (
VALUES ( JiraEpic[EpicKey] ),
VALUES ( IssuesList[EpicKey] )
) -- Not in IssuesList
) +
CALCULATE(
DISTINCTCOUNT(IssuesList[ProjectKey])
)
)

     

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey Anonymous ,

     

    please take the time to create a pbix that contains sample data, but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link.

     

    Please describe the expected result based on your sample data.

     

    Regards,

    Tom

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    Anonymous 

     

    Any chance you can post sample data from both tables? (as a table if possible, to allow us to copy/paste into Excel)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I think I solved it with EXCEPT.

      Count of Projects = CALCULATE(
CALCULATE(
DISTINCTCOUNT(JiraEpic[ProjectKey]),
EXCEPT (
VALUES ( JiraEpic[EpicKey] ),
VALUES ( IssuesList[EpicKey] )
) -- Not in IssuesList
) +
CALCULATE(
DISTINCTCOUNT(IssuesList[ProjectKey])
)
)