Forum Discussion

pnem's avatar
pnem
Frequent Visitor
3 years ago
Solved

Count items in hierarchy

Hey, I have issue in counting ItemVariant for each Item for Brand in selected Store So the hierarchie looks like Store/Brand/Item/ItemVariant from top to the bottom  I need to count first how ma...
"DAX"
DAX Commands and Tips
Tips & Trick
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  pnem ,

     

    Here are the steps you can follow：

    1. Create measure.

    failedVariant =
var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])),"itemgroup",[StockQty])
return
IF(
    [StockQty] = 0 && HASONEVALUE('Table'[item]) ,1,
    IF(
        [StockQty] <> 0&& HASONEVALUE('Table'[item]) ,0,
    IF(
         0 in _select && HASONEVALUE('Table'[brand]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])&&[StockQty]=0),[item]),
         IF(
             HASONEVALUE('Table'[name]),         COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])),[item]),
             IF(              HASONEVALUE('Table'[loc1]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])),[item]),0)))))
divide =
DIVIDE(
    [failedVariant],[StockQty])

    2. Result:

     

     

    Best Regards,

    Liu Yang

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  pnem ,

 

Here are the steps you can follow：

1. Create measure.

failedVariant =
var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])),"itemgroup",[StockQty])
return
IF(
    [StockQty] = 0 && HASONEVALUE('Table'[item]) ,1,
    IF(
        [StockQty] <> 0&& HASONEVALUE('Table'[item]) ,0,
    IF(
         0 in _select && HASONEVALUE('Table'[brand]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])&&'Table'[brand]=MAX('Table'[brand])&&[StockQty]=0),[item]),
         IF(
             HASONEVALUE('Table'[name]),         COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])&&'Table'[name]=MAX('Table'[name])),[item]),
             IF(              HASONEVALUE('Table'[loc1]),COUNTX(FILTER(ALL('Table'),'Table'[loc1]=MAX('Table'[loc1])),[item]),0)))))
divide =
DIVIDE(
    [failedVariant],[StockQty])

2. Result:

 

 

Best Regards,

Liu Yang

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