Help with counting rows based on conditions

[Infinifox]

Hi Everyone, 

I have a problem that I need help with. What I am trying to do is have 2 counts that can also be displayed within a table with these fields Work Order, Work Order Phase, Asset Tag, Count of Serialized Assets, Count of Child Assets.

An example of the data would be

Work Order	Work Order Phase	Asset Tag	Asset Type	# of Serialized Assets (legislated)	# of Child Assets (legislated)
20241010001	1	1	SERIALIZED	1	0
20241010002	1	2	SYSTEM	0	1
20241010003	1	3	SERIALIZED	1	0
20241010004	1	3	SERIALIZED	1	0
20241010004	2	4	SYSTEM	0	2

I have a WorkOrder table that is tied to the Asset table via Asset Tag. The Asset table is tied to ChildAssets and AssetDetails via Asset Tag

The first count is trying to count how many assets are serialized. If Asset[Asset Type] = "SERIALIZED" and Asset Legislated[Asset Legislated] = "Y" then count that row.

The 2nd count is trying to count if Asset[Asset Type] = "SYSTEM" then we instead need to count how many childrows there are in Child Assets that are also Asset Legislated[Asset Legislated] = "Y".

I have an example of the data as a .pbix but not sure how to share it.

[Infinifox]

In my example I do not have the # of serialized assets (legislated) and # of child assets (legislated) as fields. These are the fields that would be created with the measure.

What has to happen if an asset is SYSTEM is that I then need to go to the ChildAsset table and see what Assets are associated with that main asset. I then need to take the ChildAsset[Child Asset Tag] and then use its relationship to see if it is legislated and include it in the count.

In my example above the Work Order 2024101004 has 2 child assets that are legislated because the asset tag is 4. Asset tag 4 is a system in the Asset table so we need to go to the ChildAsset table. In the ChildAsset table there is 2 assets associated with that asset tag so we now need to go check both individually against the AssetDetails table to see if they are LEGISLATED = "Y"

This is the measure I was trying to use previously:

Count of Legislated Component Assets =

CALCULATE(

    COUNTROWS(

        SUMMARIZE(

            AssetChild,

            AssetChild[assetTag],  -- Grouping by assetTag

            "GroupedRows", COUNT(AssetChild[childAssetTag])

        )

    ),

        USERELATIONSHIP(AssetChild[childAssetTag], MasterAsset[assetTag]),

        AssetDetails[Legislated] = "Y"

)

[v-yilong-msft · Accepted solution]

Hi @Infinifox ,

I create three tables as you mentioned.

Then I think you can create a measure and here is the DAX code.

Measure = 
VAR SystemAssets =
    FILTER ( Asset, Asset[assetType] = "System" )
VAR ChildAssets =
    CALCULATETABLE (
        SUMMARIZE ( ChildAsset, ChildAsset[childAssetTag], ChildAsset[assetTag] ),
        SystemAssets
    )
RETURN
    COUNTROWS ( SystemAssets )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yilong-msft]

Hi @Infinifox ,

For some new community members, they don't have the permission to upload .pbix files. You may need to share file links via DropBox, One Drive, Drive or any other tool.

I also create a table as you mentioned.

Then I think you can use the DAX codes below.

Measure = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    'Table'[Asset Type] = "SERIALIZED",
    'Table'[# of Serialized Assets (legislated)] = 1
)

Measure 2 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    'Table'[Asset Type] = "SYSTEM",
    'Table'[# of Serialized Assets (legislated)] = 0,
    'Table'[# of Child Assets (legislated)] <> 0
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.