Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AV_04
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
Samarth_18
Community Champion
Community Champion

@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:-

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

6 REPLIES 6
Samarth_18
Community Champion
Community Champion

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

image.png

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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
Community Champion
Community Champion

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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
Community Champion
Community Champion

@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:-

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Thank you very much samarth. This one works!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors