topic Re: ALLSELECTED OR NOT? in DAX Commands and Tips

ALLSELECTED OR NOT?

faldiwan — Sun, 21 Apr 2024 21:44:38 GMT

Hi all,

I need some help please, I have a simple tabular model.
3 tables Portfolio, Position & Date.

The Portfolio table is pretty much a type 1 dimension, Position is a fact table.

The visual I have contains data from mainly the portfolio table and 2 measures from the Position and a date selected. There is only one filter from the Portfolio table, where they selected 2 companies out of the list of 5 companies.

Company	Investment	Value	Overall Value
X	A	100	1100
Y	B	1000	1100

This is the data i get and its fine, but the issue is that, I want to be able to keep selecting the 2 companies and if I put an extra filter to select 1 investment, id like to see the overall value stay as 1100 because ive selected the 2 companies.

The DAX I have used for Overall Value = CALCULATE( SUM(Value), REMOVEFILTERS(Portfolio), ALLSELECTED(Portfolio[Company]))

This works fine, when all investments appear, but when filtering down to 1, it only gives me the overall value of the company within the investment. Dont understand why? It looks like the below:

Company	Investment	Value	Overall Value
X	A	100	100

Where I would like to keep seeing 1100 in the overall value.

Any help would be much appreciated.

Re: ALLSELECTED OR NOT?

lbendlin — Sun, 21 Apr 2024 23:16:40 GMT

You need to do REMOVEFILTERS/ALLSELECTED on the investment as well. And if you have a sort column then you need to include that too etc.

Re: ALLSELECTED OR NOT?

faldiwan — Mon, 22 Apr 2024 06:40:19 GMT

I did CALCULATE( SUM(Amont), Removefilters(InvestmentCode), ALLSELECTED(Company))

Still only returns the amount within the company in the investment

Re: ALLSELECTED OR NOT?

lbendlin — Mon, 22 Apr 2024 10:53:54 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: ALLSELECTED OR NOT?

faldiwan_ar — Mon, 22 Apr 2024 11:50:06 GMT

Position table:

PortfolioId	TotalValue
1	1000
2	2000
3	3000

Portfolio table:

Id	Company	Investment
1	Test1	Apple
2	Test2	Amazon
3	Test3	Arm

Report:

Company	Investment	Total Value	Overall
Test1	Apple	1000	4000
Test3	Arm	3000	4000

If I use the following day for Overall:
CALCULATE( SUM(TotalValue), REMOVEFILTERS(Portfolio[Investment]), ALLSELECTED(Portfolio[Company])

I get the Report table above, which is great, BUT if i only select "Investment = A" in the filter "Filters on this visual" I only get Overall = 1000, because it looks like its summing within the selected investments.

Model example:

Works when selecting 2 companies and all investments within the companies.

When then selecting only 1 investment, should expect to see 4000, but shows only value within the 1 investment selected, need to see the 4000

Re: ALLSELECTED OR NOT?

lbendlin — Tue, 23 Apr 2024 12:33:39 GMT

Re: ALLSELECTED OR NOT?

faldiwan_ar — Tue, 23 Apr 2024 12:38:19 GMT

Hi,

Im expecting to see 4000, not 6.

So ive selected Apple like you did and in the company filters selected Company Test1 & Test2, so i should expect to see the sum of the values for both Test1 & Test2.

Your result is returning all 3 companies, when i only selected 2 in my filter

Re: ALLSELECTED OR NOT?

lbendlin — Tue, 23 Apr 2024 13:14:53 GMT

That's a "wash me but don't make me wet" scenario. You need to use disconnected tables and measures.

Re: ALLSELECTED OR NOT?

faldiwan_ar — Tue, 23 Apr 2024 13:18:22 GMT

Hi,

I was hoping I wouldnt need to remodel, but i assumed that would be the case.

Thanks for helping