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

pnem's avatar
pnem
Frequent Visitor
3 years ago

hey Anonymous 

I have created measure which return count for each scope in "hierarchy" 

Measure 5 = 
SWITCH(true(),
ISINSCOPE(DimItem[Item Code]),[MeasureITEMVARIANTID],
ISINSCOPE(DimItem[Brand]),[MeasureITEMID],
ISINSCOPE(DimLocation[Location Name]),[MeasureBRANDID]) 


MeasureITEMVARIANTID
MeasureITEMID
MeasureBRANDID 

are distinccount() for each column





Now I want to count how many 0 are in each scope

Eg. for this item in example i want to have for each 0 null and above number 4  - first level of hierachy 
than if we collapse item to brand same thing - just to count 0 for each scope 

hope I was clear 


Please advice, thanks!

Regards Nem

pnem's avatar
pnem
Frequent Visitor
3 years ago

Still facing the issue, any advice?