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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sbm_tekade
Frequent Visitor

Wanted help to understand DAX with selection criteria

I have the following table having customer A's monthly transactional data-

sbm_tekade_0-1677053597344.png

 

and I wanted to add an additional column in the same table using DAX or M-language like below-

 

sbm_tekade_2-1677052750535.png

 

the condition is that if the user selects any month on top of the report we want to list the product newly added to the customer bucket list as compared to the previous month only.

in this scenario, feb-23 is selected so it is compared to Jan-23 list of products.

in Jan-23, customer purchase ={ Bread, Butter, Bear, Chicken}

and Feb-23 having purchase={ Wine, Eggs, Bread, Butter, Chicken}

so I want to mark flag {Wine, Eggs} as 1 otherwise 0.

 

Anybody can please help me to solve this out.

 

Thanks

 

 

 

1 ACCEPTED SOLUTION

@sbm_tekade 

 

Here's one way to solve it. If you always want to compare to prior month, I guess you could do this as a column too, but in doing it as a measure you have the flexibility to change the date range to 'bought in any date before selected' rather than just narrowing to the prior month. 

 

AllisonKennedy_0-1677304789168.png

 

 

Bought Prior Month Flag =
Var _CurrentEndOfMonth = EOMONTH(MAX(dimDates[Date]),0)
Var _PriorEndOfMonth = EOMONTH(MAX(dimDates[Date]),-1)
VAR _Product = SELECTEDVALUE(SampleData[ProductID])
VAR _PriorMonthProducts = CALCULATETABLE( VALUES(SampleData[ProductID]),FILTER(ALL(dimDates), dimDates[End of Month] = _PriorEndOfMonth))
VAR _Result =
SWITCH(TRUE(),
_Product in _PriorMonthProducts, "Yes",
"No"
)
RETURN
IF(HASONEVALUE(SampleData[Product Name]) && HASONEVALUE(dimDates[End of Month]), _Result)
 
 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
AllisonKennedy
Super User
Super User

@sbm_tekade  You cannot achieve this using a calculated column. Columns never reference the value of a slicer, as they are already precalculated before the slicer selection: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html

 

You need to do this as a measure, then you can add the measure to a table visual in Power BI and use that measure as a visual level filter if you need. 

 

Please paste your sample data in as a table that we can copy paste if you want more help on how to create that measure. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hello @AllisonKennedy ,

Thanks for the quick response.

Yes understood that I need to create a measure for this.

I would be really grateful if you can help me with DAX a little bit.

here is the sample data below separated with white spaces.

 

Month-Year Product_id Product_name Cost
Jan-23 1 Bread 40
Jan-23 2 Butter 110
Jan-23 4 Bear 230
Jan-23 6 Chicken 290
Feb-23 5 Wine 560
Feb-23 1 Bread 45
Feb-23 3 Eggs 90
Feb-23 2 Butter 100
Feb-23 6 Chicken 350
Mar-23 2 Butter 120
Mar-23 6 Chicken 310
Mar-23 4 Bear 200
Mar-23 1 Bread 35

@sbm_tekade 

 

Here's one way to solve it. If you always want to compare to prior month, I guess you could do this as a column too, but in doing it as a measure you have the flexibility to change the date range to 'bought in any date before selected' rather than just narrowing to the prior month. 

 

AllisonKennedy_0-1677304789168.png

 

 

Bought Prior Month Flag =
Var _CurrentEndOfMonth = EOMONTH(MAX(dimDates[Date]),0)
Var _PriorEndOfMonth = EOMONTH(MAX(dimDates[Date]),-1)
VAR _Product = SELECTEDVALUE(SampleData[ProductID])
VAR _PriorMonthProducts = CALCULATETABLE( VALUES(SampleData[ProductID]),FILTER(ALL(dimDates), dimDates[End of Month] = _PriorEndOfMonth))
VAR _Result =
SWITCH(TRUE(),
_Product in _PriorMonthProducts, "Yes",
"No"
)
RETURN
IF(HASONEVALUE(SampleData[Product Name]) && HASONEVALUE(dimDates[End of Month]), _Result)
 
 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

sbm_tekade
Frequent Visitor

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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