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 ,

You can modify it to the following dax:

Flag =
IF(
    ISINSCOPE('Flag'[ItemVariant]),MAX('Flag'[Value]),
IF(
    ISINSCOPE('Flag'[Item]),
    FORMAT(
    DIVIDE(
        COUNTX(
            FILTER(ALL(Flag),            'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])&&'Flag'[Item]=MAX('Flag'[Item])&&'Flag'[Value]=0),[ItemVariant]),
        COUNTX(
            FILTER(ALL(Flag),          'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])&&'Flag'[Item]=MAX('Flag'[Item])),[ItemVariant])),"Percent"),
IF(
    ISINSCOPE('Flag'[Brand]),
     COUNTX(
            FILTER(ALL(Flag),
            'Flag'[Store]=MAX('Flag'[Store])&&'Flag'[Brand]=MAX('Flag'[Brand])),[ItemVariant]),
IF(
    ISINSCOPE('Flag'[Store]),
    COUNTX(
            FILTER(ALL(Flag),
            'Flag'[Store]=MAX('Flag'[Store])),[ItemVariant]),
    COUNTX(ALL(Flag),[Value])))))

 

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?