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          Bou...
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:-

     

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?

AV_04's avatar
AV_04
Helper I
3 years ago

Thanks samarth. I am halfway through the solution, I just need the distinct count of sub category that goes out of stock. Here in the above table the count will be only one (Lewis), incase if any other sub category within product satisfies the condition, we need to do count+1. I am not sure how to draw the resultant up in a table.

 

Product                      Sub-Product         Bought by customer?           Stocks Available
Sportslewis14
Sportslewis14
Sportslewis14
Sportslewis14
SportsJ112
SportsJ112
accessoriesJacks13
accessoriesJacks13
Sportschrome12
Sportschrome12

 

For the above table the resultant count should be 3 since lewis, j1 and chrome satisfies the condition. 

I hugely thank you for the solution. I just started with my advanced dax journey hopefully can catch up soon.

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    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:-

     

    • AV_04's avatar
      AV_04
      Helper I
      3 years ago

      Thank you very much samarth. This one works!