Forum Discussion
DAX query: create Measure that selects between two options depending on filter selection
hi all, I have a DAX query that I hope someone might be able to help me with.
To describe the simplified use case: suppose I have a range of varieties A-F with Ratings 1-3 as per below:
On the dashboard is a filter that enables the user to select one or more Varieties.
I want to calculate an overall rating that takes into account the individual ratings 1,2 and 3, but due to variety A and B missing values for Rating 1 I can't simply compare based on the totals. So suppose that instead what I would like to do is the following:
- if the varieties selected by the user in the filter on the dashboard include variety A or B (or both), then the overall rating should be the total of Rating 2 and Rating 3. As a DAX measure: Measure INCOMPLETE = MAX('Table'[Rating 2]) + MAX('Table'[Rating 3])
- if the varieties selected by the user do NOT include variety A or B, then the overall rating should be the total of Rating 1, 2 and 3. As a DAX measure: Measure COMPLETE = MAX('Table'[Rating 1]) + MAX('Table'[Rating 2]) + MAX('Table'[Rating 3])
I then tried to implement the overall rating in DAX as follows:
but this doesn't work. In the screenshot below the [Measure FINAL] column should show the values of Measure INCOMPLETE because variety A is included. Is there another way to achieve this?
.pbix file available from here: https://gofile.io/d/qcHudd
Many thanks, Bastiaan
Hi Liu, thanks for taking the time to respond and including sample workbook, your solution does most of the job but it's not quite providing me with exactly the required solution. As it is, the measure sums the Ratings of all varieties included (see screenshot below) whereas I need a summed rating per variety.
So when Variety A, B and C have been selected as per your screenshot, instead of the measure giving 43 to each variety, it should give 15 to variety A, 14 to variety B and 14 to variety C.
By amending your measure to the following it gives me the required solution though:
Measure_COMPLETE =IF(MINX(ALLSELECTED('Table'),[Variety])<="B",MAX('Table'[Rating 2]) + MAX('Table'[Rating 3]),MAX('Table'[Rating 1]) + MAX('Table'[Rating 2]) + MAX('Table'[Rating 3]))Thanks, Bastiaan
4 Replies
Hi BastiaanBrak ,
Here are the steps you can follow：
1. Create measure.
Measure_Sersult = IF( MINX(ALLSELECTED('Table'),[Variety])<="B", SUMX(ALLSELECTED('Table'),[Rating 2]+[Rating 3]), SUMX(ALLSELECTED('Table'),[Rating 2]+[Rating 3]+[Rating 1]))2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Liu, thanks for taking the time to respond and including sample workbook, your solution does most of the job but it's not quite providing me with exactly the required solution. As it is, the measure sums the Ratings of all varieties included (see screenshot below) whereas I need a summed rating per variety.
So when Variety A, B and C have been selected as per your screenshot, instead of the measure giving 43 to each variety, it should give 15 to variety A, 14 to variety B and 14 to variety C.
By amending your measure to the following it gives me the required solution though:
Measure_COMPLETE =IF(MINX(ALLSELECTED('Table'),[Variety])<="B",MAX('Table'[Rating 2]) + MAX('Table'[Rating 3]),MAX('Table'[Rating 1]) + MAX('Table'[Rating 2]) + MAX('Table'[Rating 3]))Thanks, Bastiaan
Please try this expression
New Measure =
VAR AorB =
COUNTROWS (
INTERSECT (
ALLSELECTED ( 'Table'[Variety] ),
{
"A",
"B"
}
)
) > 0
RETURN
IF (
AorB,
MAX ( 'Table'[Rating 2] )
+ MAX ( 'Table'[Rating 3] ),
MAX ( 'Table'[Rating 1] )
+ MAX ( 'Table'[Rating 2] )
+ MAX ( 'Table'[Rating 3] )
)Regards,
Pat
Hi BastiaanBrak ,
If this answer helps you, please mark it if you can
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
