Forum Discussion

Infinifox's avatar
Infinifox
Frequent Visitor
1 year ago
Solved

Help with counting rows based on conditions

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, ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Infinifox's avatar
Infinifox
Frequent Visitor
1 year ago

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"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.