How can I get 0 count for missing component

[EaglesTony]

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   

[v-karpurapud]

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]

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

[v-karpurapud · Accepted solution]

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.
 

[EaglesTony]

Is there a way to show them across a single row instead of multiple rows ?

I was thinking Groupby ?

[v-karpurapud]

Hi @EaglesTony 

If possible, try to download the PBIX file to your personal device. This will help you have a better understanding of the logic.

[Ashish_Excel]

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.

[Bmejia]

You can create a measure for each category as follows

AppCode = IF(
CALCULATE(
SELECTEDVALUE(IssueComponents[Key]),
IssueComponents[Component]="AppCode"),"Y","N")

[EaglesTony]

A measure on what table ?

[Bmejia]

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.