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 ,

 

Sorry, can you present the expected result in the form of a picture, because the data I created with you and I will be different, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

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 

    as I already posted - 

     

     

    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

     

     


    So this value is retriving number of subcategory for each scope. 

    I have measure [stock qty] and I want for each category to have some kind of a filter where [stock qty] = 0 

    want to sum all subcategories where [stock qty] = 0 

    Eg - 

     

    variantQty is measure 5 and I want to create failedVariant measure for each level of "hierachy" 
    Then I will calculate further in report like 

     

     

    divide(failedVariant/measure5)

     

     



    Want to point out that I am using model (SSAS) and I am not able to create calculated columns... 

    Hope you can understand now better the issue.