Forum Discussion

AV_04's avatar
AV_04
Helper I
3 years ago
Solved

DAX Help

Hi team,

 

Could anyone help me with the below dax requirement? I have a table with product, sub-product, Quantity Sold and Stock available. 

Product                 Sub-Product         Bought by customer?                 Stocks Available                      
Sportslewis14
Sportslewis14
SportsLewis14
SportsLewis14
SportsJ115
SportsJ115
accessoriesJacks13
accessoriesJacks13

 

I need to find out the sub product that has no stocks. For example, under sports product- Lewis sub category the  count of Yes in the bought by customer field is equal to the value in stocks available, so I need to increase the count to 1, while for j1 the condition didn't satisfy so the count need not be increased.


I really appreciate if anyone can help me with the requirement.

 

Thank you!

Need Help
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    AV_04 , Okay then please create a column as below:-

    _no_stocks_left = 
VAR cust_bought =
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Sub-Product] = EARLIER ( 'Table'[Sub-Product] )
        )
    )
VAR stock_available =
    CALCULATE (
        MAX ( 'Table'[Stocks Available] ),
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Sub-Product] = EARLIER ( 'Table'[Sub-Product] )
        )
    )
VAR _result = stock_available - cust_bought
RETURN
    IF ( _result = 0, 1,0 )

     

    Then create a measure as below:-

    Measure = 
CALCULATE(DISTINCTCOUNT('Table'[Sub-Product]),FILTER('Table','Table'[_no_stocks_left] = 1))

     

    Refer a file below:-

     

6 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi AV_04 ,

     

    If I undestand your question correctly then below would be your code:-

    _stocks =
VAR cust_bought =
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Sub-Product] = EARLIER ( 'Table'[Sub-Product] )
        )
    )
VAR stock_available =
    CALCULATE (
        MAX ( 'Table'[Stocks Available] ),
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Sub-Product] = EARLIER ( 'Table'[Sub-Product] )
        )
    )
VAR _result = stock_available - cust_bought
RETURN
    IF ( _result = 0, stock_available + 1, stock_available )

     

    Output:-

    • AV_04's avatar
      AV_04
      Helper I
      3 years ago

      Hi Samarth, 

       

      Thanks for your response, Apologies if my question wasn't clear. My requirement is just to count the sub category. For example, in the sub category field of lewis, the count of 1 in bought by customer field is 4 which is equal to the value in stocks available(4), only when they are equal I need to consider and count those instances. Could you kindly let me know if my question wasn't clear, I can try to rephrase it again. Thanks for your help.

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        3 years ago

        Above code satify this condition right?
        Could you please share your final required output based on provided data?