Forum Discussion
Assigning a group from.
Hi Sahil5791 ,
You're almost there. Since your Assign table isn't directly related to the Calendar or Sales table, using a measure like the one below is a good approach. Here's a slightly refined version that might help with clarity and performance:
Sales by Assigned Group = VAR selectedDate = SELECTEDVALUE('Calendar'[Date]) RETURN CALCULATE( SUM('Sales'[SalesAmount]), FILTER( 'Assign', 'Assign'[StartDate] <= selectedDate && 'Assign'[EndDate] >= selectedDate ) )Make sure your visual includes the 'Group' column from the Assign table. This way, the measure will evaluate the sales amount for each group where the selected date falls between the start and end dates.
Also, if your Sales table has a group reference, you might need to adjust the logic to join that with Assign, or use TREATAS to simulate a relationship.
Let me know if you need help wiring the tables together or optimizing the model.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
Sahil5791
try this
CustomerDiscountGroup =
VAR CurrentAccountType = SELECTEDVALUE(Sales[AccountType])
VAR CurrentCustomer = SELECTEDVALUE(Sales[Customer])
VAR CurrentSales = [SalesAmount] -- your dynamic sales measure
VAR DiscountRow =
FILTER(
PricingSheet,
PricingSheet[AccountType] = CurrentAccountType &&
CurrentSales >= PricingSheet[MinVolume] &&
CurrentSales <= PricingSheet[MaxVolume]
)
VAR Result =
SELECTEDVALUE(DiscountRow[DiscountGroup], "No Group")
RETURN
Result
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Thank you for the dax. It is working for now as there is only one quarter data in pricing sheet.just want to confirm and make sure the dax will dynamically adjust for chaning quarter meaning the discount group change on quarterly basis and is updated in the backend in the pricing sheet and it is an in incremental load. So this dax should detect the account type, quarter, sales and accordingly assign the discount group for the selected quater.