Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
1 year ago
Solved

How can I get 0 count for missing component

I have the following table called "tblComponentName (2) with the following field and data:

 

ComponentName

AppCode

Stakeholder

Technology

 

I have another table called "IssueComponents" with the following field and data:

 

Key       ComponentName

123       AppCode

123       Stakeholder

123       Technology

456       Stakeholder

 

What I need is to identify that 456 doesn't have AppCode and Technology, so I would want something like:

 

Key       AppCodeComponentPresent       StakeholderComponentPresent     TechnologyComponentPresent

123        Y                                                  Y                                                      Y

456        N                                                  Y                                                     Y

 

I think some sort of grouping would do this with a zero count ????

 

Thank you   

 

Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi EaglesTony 

    1. Create distinct lists of keys table and components table using DAX:

    For key table

KeysList = DISTINCT('IssueComponents'[Key])

For Component Table
ComponentsList = DISTINCT('tblComponentName (2)'[ComponentName])


    2. Create another table that performs a cross join of keys and components:

    KeyComponentMatrix = 
GENERATE(
    KeysList,
    SELECTCOLUMNS(ComponentsList, "ComponentName", ComponentsList[ComponentName])
)


    3. In the KeyComponentMatrix, add a calculated column to evaluate whether each component is present for a given key:

     

    ComponentPresent = 
IF (
    CALCULATE (
        COUNTROWS('IssueComponents'),
        FILTER (
            'IssueComponents',
            'IssueComponents'[Key] = KeyComponentMatrix[Key]
                && 'IssueComponents'[ComponentName] = KeyComponentMatrix[ComponentName]
        )
    ) > 0,
    "Y",
    "N"
)


    I have attached a snapshot for the tables i have created , please review it.


    If this post helps , kindly mark it as Accepted Solution. Appreciate your Kudos.



    Regards,
    Karpurapu D.
     

     

9 Replies

  • Bmejia's avatar
    Bmejia
    Super User
    1 year ago

    You can create a measure for each category as follows

    AppCode = IF(
    CALCULATE(
    SELECTEDVALUE(IssueComponents[Key]),
    IssueComponents[Component]="AppCode"),"Y","N")
      • Bmejia's avatar
        Bmejia
        Super User
        1 year ago

        I have update my measure the table name base on what you provided, but believe if you only have a few keys that would be fine if you going to be adding alot more "v-karpurapud"  example would be more dynamic where if new keys are provided it would be done automatically rather then you having to create each measure everytime.

  • Ashish_Excel's avatar
    Ashish_Excel
    Solution Supplier
    1 year ago

    Hi,

    Create a relationship (Many to One and Single) from Table 2 to Table 1.  To your matrix visual, drag Key from Table 2 in the rows bucket and the the single field from Table 1 in the columns bucket.  Write this measure

    Measure = countrows('Table2')

    Hope this helps.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi EaglesTony

    Welcome to the Microsoft Fabric Forum. Also thank you Ashish_Excel and Bmejia for your quick response.
     

    To obtain a count of zero for a missing component,  I have created a sample logic below to demonstrate one possible approach to implementing the desired output in Power BI.

     

    While I may not have full visibility into the specific structure of your dataset, I’ve included relevant screenshot and attached the .pbix file for your reference. Please take a moment to review them and see if this solution aligns with your requirements.

     

    If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.
     

    If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.
     

     

    Thank you!
     

    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      1 year ago

      I can't download the .pbix due to business reasons. Is there a code snippet here of how you did it ?

      • v-karpurapud's avatar
        v-karpurapud
        Community Support
        1 year ago

        Hi EaglesTony 

        1. Create distinct lists of keys table and components table using DAX:

        For key table

KeysList = DISTINCT('IssueComponents'[Key])

For Component Table
ComponentsList = DISTINCT('tblComponentName (2)'[ComponentName])


        2. Create another table that performs a cross join of keys and components:

        KeyComponentMatrix = 
GENERATE(
    KeysList,
    SELECTCOLUMNS(ComponentsList, "ComponentName", ComponentsList[ComponentName])
)


        3. In the KeyComponentMatrix, add a calculated column to evaluate whether each component is present for a given key:

         

        ComponentPresent = 
IF (
    CALCULATE (
        COUNTROWS('IssueComponents'),
        FILTER (
            'IssueComponents',
            'IssueComponents'[Key] = KeyComponentMatrix[Key]
                && 'IssueComponents'[ComponentName] = KeyComponentMatrix[ComponentName]
        )
    ) > 0,
    "Y",
    "N"
)


        I have attached a snapshot for the tables i have created , please review it.


        If this post helps , kindly mark it as Accepted Solution. Appreciate your Kudos.



        Regards,
        Karpurapu D.