ALLSELECTED Duplicating Table Output

[steveplatz]

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!

[v-lili6-msft · Accepted solution]

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

[steveplatz]

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.

[kentyler]

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_market

in 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 question

[steveplatz]

@kentyler 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?

[kentyler]

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_market

That seems to take both a store slicer and an iscurrent slicer into account

[steveplatz]

This works when filtered to a specific market, but not when all markets are displayed. I changed the MAX for "Market 2" to be different than Market 1 and get this result:

What I'd like to see is 140 for every cell in MaxSelectedSales2.

[kentyler]

Maybe you need to remove the filter on just one market in line 3.