Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lionx
Helper I
Helper I

Correct measure to count number of components in BOM structure

Could you please help me to correct the measure to show the number of components in each layer of BOM as described in the picture? 

 

lionx_1-1682556697267.png

 

I have created the two measures below but they do not works. I also attached the link to download pbi desktop file for your inspection. 

 

Total items =
VAR Val = DISTINCTCOUNTNOBLANK(flattenHierrachy[no])
VAR EntityShowRow =
    [BrowseDepth] <= [BrowseDepth]
VAR Result =
    IF ( EntityShowRow, Val )
RETURN
    Result
 
 
Count components of earlier level =
IF (
    [BrowseDepth] > [MaxNodeDepth],
    BLANK (),
        IF([BOM] = "L0", DISTINCTCOUNTNOBLANK( 'flattenHierrachy'[Level 1] ),
            IF([BOM] = "L1", DISTINCTCOUNTNOBLANK( 'flattenHierrachy'[Level 2] ),
                IF([BOM] = "L2", DISTINCTCOUNTNOBLANK( 'flattenHierrachy'[Level 3] ),
                    IF([BOM] = "L3", DISTINCTCOUNTNOBLANK( 'flattenHierrachy'[Level 4] ),
                        IF([BOM] = "L4", DISTINCTCOUNTNOBLANK( 'flattenHierrachy'[Level 5] ),
                            IF([BOM] = "L5", DISTINCTCOUNTNOBLANK( 'flattenHierrachy'[Level 6] ),
                                DISTINCTCOUNTNOBLANK( 'flattenHierrachy'[Assembly Item] )
)))))))
 

The link to download the .pbix file  
https://bit.ly/3HhW8WE

1 ACCEPTED SOLUTION

Hi Ibendlin,

 

Thank you very much for your time. I have corrected the issue by myself with the same logic as the code block above. 

 

 

 

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

You have a couple of problems. One of them is that you don't really have a hierarchy.

 

lbendlin_0-1682805506407.png

 

As you can see a substantial number of items have multiple parents.  That's a no-no in a hierarchy.

 

You are better off with a snowflake style data model where you describe for each actual product what their BOM is, and then for these components what their sub-BOM is etc.

Hi Ibendlin,

 

Thank you very much for your time. I have corrected the issue by myself with the same logic as the code block above. 

 

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.