Forum Discussion
Post ProdigyHow can I get 0 count for missing component
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.
Community SupportHi 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!
Post ProdigyI can't download the .pbix due to business reasons. Is there a code snippet here of how you did it ?
- v-karpurapud
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.