cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sreeprasad Visitor
Visitor

Need help to create a DAX query for a column

Hi,

 

I have a table with few columns. I want to create a column by checking check whethere the shop is "ADD_TO_CART" THEN Number, when shop is "PRODUCT_VIEW" THEN the Number of ADD_TO_CART- Number of PRODUCT_VIEW, WHEN shop is "CHECKOUT"  THEN the Number of PRODUCT_VIEW- Number of CHECKOUT. Could anyone please help me on this?

Capture1.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Need help to create a DAX query for a column

Hi @sreeprasad,

Based on my test, you could refer to below formula:

Column = IF([Shop]="PRODUCT_VIEW",
    CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="ADD_TO_CART"&&'Table1'[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="PRODUCT_VIEW"&&'Table1'[Date]=EARLIER(Table1[Date]))),
         IF([Shop]="CHECKOUT",
             CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="PRODUCT_VIEW"&&'Table1'[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="CHECKOUT"&&'Table1'[Date]=EARLIER(Table1[Date])))
             ,[Number]))

Result:

1.PNG

You could also download the pbix file to have a view:

https://www.dropbox.com/s/3jw6hqb5o4ib8uh/Need%20help%20to%20create%20a%20DAX%20query%20for%20a%20co...

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Need help to create a DAX query for a column

Hi @sreeprasad,

Based on my test, you could refer to below formula:

Column = IF([Shop]="PRODUCT_VIEW",
    CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="ADD_TO_CART"&&'Table1'[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="PRODUCT_VIEW"&&'Table1'[Date]=EARLIER(Table1[Date]))),
         IF([Shop]="CHECKOUT",
             CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="PRODUCT_VIEW"&&'Table1'[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="CHECKOUT"&&'Table1'[Date]=EARLIER(Table1[Date])))
             ,[Number]))

Result:

1.PNG

You could also download the pbix file to have a view:

https://www.dropbox.com/s/3jw6hqb5o4ib8uh/Need%20help%20to%20create%20a%20DAX%20query%20for%20a%20co...

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.