Central NJ Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
  • 1.  DID example

    Gold Contributor
    Posted Aug 08, 2018 10:36 PM
    Hello CNJ User Group,

    I'll tell you something you probably know -- DAX is Difficult (DID).
    Here's a DID that we use a lot and that may be the most popular DAX among our members.

    The question is:
    How to compare two data sets and tell the which entries are not represented in both.

    Example:
    One data set is a list of names or unique identifiers.
    Call this data set: "Names".  Imagine that it has 100 names or nameIDs.
    The other data set is a list of activities involving some names in NameSet.
    Call this data set: "Activities".  Imagine that it shows an activity for 85 different names in "Names"
    What is a way to find out the names that are not represented in the activities list?
    In this simple example, we know the answer is 15 names, but what are they?

    This DAX is useful for this type of question.  And there are lots of variations on the question.

    ActivityTrue = CALCULATE(COUNTROWS(Activities), FILTER (Activities, Activities[NameID] = EARLIER(Names[NameID]) ) ) > 0

    To use this DAX, add a column to the dataset called "Names" in Power BI Desktop.
    Paste (or enter) the DAX in the formula space above the visuals.
    Click the check mark to save the column, which will appear in the Fields section within the expansion of the columns for the Names dataset.

    The DAX returns a value of True or False.
    True indicates that the NameID in the Names data set appears in the Activities data set.
    False indicates that the NameID in the Names data set does not appear in the Activities data set.

    In many instances, we may want to know which names are not represented in another data set.
    In this example, we may want to know about those 15 names not represented in the Activities data set.
    This DAX will tell us.

    Come to the PUG meeting on Wed, Aug 29, 6-8pm and we will practice with this DAX.
    You will feel very powerful afterwards. :-)

    Hope to see you in on the 29th.
    More DID before DIE (more DAX is Difficult before DAX is Easy).

    ------------------------------
    Jerome Scriptunas
    Central NJ PUG
    ------------------------------