Forum Discussion
ALLSELECTED Duplicating Table Output
Hi everyone,
I've got a problem that I can't seem to find a resolution to that I'm hoping you can help with. What I'm trying to do is calculate the MAX value for all selected rows and display that in a table alongside the actual value for that row. What's happening is that the MAX value is calculated as expected, but rows in the table are being duplicated. I'll start with my data model.
DimMarket
| Id | MarketName |
| 1 | Market 1 |
| 2 | Market 2 |
FactSales
| Id | MarketId | Sales | IsCurrent |
| 1 | 1 | 100 | True |
| 2 | 2 | 50 | True |
| 3 | 1 | 50 | True |
| 4 | 2 | 100 | True |
| 5 | 1 | 60 | True |
| 6 | 2 | 40 | True |
| 7 | 1 | 30 | False |
| 8 | 2 | 20 | False |
| 9 | 1 | 70 | False |
| 10 | 2 | 120 | False |
Relationships
Measure
MaxSelectedSales = CALCULATE(MAX(FactSales[Sales]), ALLSELECTED(FactSales))
Output
Expected Output
| Id | MarketName | Sales | MaxSelectedSales |
| 1 | Market 1 | 100 | 100 |
| 2 | Market 2 | 50 | 100 |
| 3 | Market 1 | 50 | 100 |
| 4 | Market 2 | 100 | 100 |
| 5 | Market 1 | 60 | 100 |
| 6 | Market 2 | 40 | 100 |
Does anyone know how I can achieve the expected results given the current data model?
Thanks in advance!
hi steveplatz
You could just adjust it as below:
Step1:
Adjust the formula as below:
New MaxSelectedSales = CALCULATE(MAX(FactSales[Sales]),ALLSELECTED(FactSales),VALUES(DimMarket[MarketName]))Step2:change the cross filter direction from "Single" to "Both"Result:
and here is sample pbix file, please try it.
Regards,
Lin
7 Replies
This what my measure ended up like
Max Sales per Market =
VAR cur_Market =
SELECTEDVALUE ( Sales[MarketId] )
VAR total_sales_per_market =
CALCULATE ( MAX ( Sales[Sales] ), ALL ( Sales ), Sales[MarketId] = cur_Market )
RETURN
total_sales_per_marketin a problem like this the first step is to get the value you want to filter by out of the current filter context-- in this case SELECTEDVALUE() does that-- and store it in a variable
the next step is to do your calculation-- inside the CALCULATE() I use ALL() to remove any filters on the Sales table coming from the external filter context (like the one from the table visual that gets the values for a specific row) and use the saved variable as a filter to get only the rows I want the MAX of
Power BI is easy to learn, but it has hard parts.
I'm a personal Power Bi Trainer I learn something every time I answer a questionkentyler Thanks for the detailed response. That makes sense and works if I'm looking for the max per market, but what I'm trying to accomplish is to get the MAX value of all selected records, taking any outside filters into account. This is a simplified example with only the IsCurrent and Market filters, but there could be much more.
Do you know how I would accomplish that?
You can change to ALLSELECTED
Max Sales per Market = VAR cur_Market = SELECTEDVALUE(Sales[MarketId])VAR total_sales_per_market = CALCULATE(max(Sales[Sales]),ALLSELECTED(Sales),Sales[MarketId]=cur_Market) return total_sales_per_marketThat seems to take both a store slicer and an iscurrent slicer into account
hi steveplatz
You could just adjust it as below:
Step1:
Adjust the formula as below:
New MaxSelectedSales = CALCULATE(MAX(FactSales[Sales]),ALLSELECTED(FactSales),VALUES(DimMarket[MarketName]))Step2:change the cross filter direction from "Single" to "Both"Result:
and here is sample pbix file, please try it.
Regards,
Lin
That's close, but the issue is the filter on market name. The original example I provided had a max of 100 for market 1 and market 2, so it makes the output look correct, but if you change the max of market 2 to 140, this is what you end up with.
