[DAX] ALLSELECTED GIVE THE WRONG ANSWER WHEN CHOOSE MULTIPLE SELECTION

[citrahemas_]

I want to display the Result value obtained from [% Favorable] measure in the previous year. But, each company has a different survey year. Here I tried to give you sample data, hope this will helps.

Dim_Company Table

id_companyid	companyname
1	A
2	B
3	C
4	D
5	E

ref_calendar table

year
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026

fact_engagementprofile table

id_companyid	tahunsurvey	countfavorableanswer	countanswer
1	2019	10989	14190
1	2023	12012	18060
2	2020	84249	134160
2	2021	724860	878232
2	2022	672417	786642
3	2023	789972	928284
4	2020	52812	65016
4	2022	12237	14448
4	2023	1349016	1706412
5	2021	1542681	1792842
5	2023	1418295	1622304

here is the DAX :

% of favorable = SUM(fact_engagementprofile[countfavorableanswer])/SUM(fact_engagementprofile[countanswer])

Prev. Survey (fav) =

Var SelectedYear = SELECTEDVALUE(ref_calendar[year])

Var PrevYear = CALCULATE(MAX(fact_engagementprofile[tahunsurvey]),ALLSELECTED(ref_calendar),ALLSELECTED(Dim_Company[companyname]),(ref_calendar[year] < SelectedYear))

Var Result =

CALCULATE(

    [% of favorable],

    (ref_calendar[year]=PrevYear)

)

RETURN Result

So the expected results, when I select all companies and 2023 in the slicer, what will appear is the % favorable value from the previous survey year (before 2023) that the company had. If the company doesn't have the year selected (2023) then the value doesn't count.

The following is an example of the calculation in Excel: company B doesn't count because it doesn't have the year 2023. Meanwhile company C has the year 2023 but doesn't have a prev survey so it doesn't count either.

So far, if I only choose one company, the results are correct. but when I selected all companies I thought the value started to be strange

Here's the .pbix file link :
https://drive.google.com/file/d/1-rcIyoQNI5SD8iU3A-HVNXjJHFV-u7n7/view?usp=sharing

[v-xinruzhu-msft]

Hi @citrahemas_ 

You can refer to the follwing measure

Measure =
VAR a =
    SUMMARIZE (
        CALCULATETABLE (
            SUMMARIZE (
                ALL ( fact_engagementprofile ),
                [id_copmanyid],
                "MaxYear",
                    CALCULATE (
                        MAX ( fact_engagementprofile[tahunsurvey] ),
                        fact_engagementprofile[id_companyid]
                            IN VALUES ( fact_engagementprofile[id_companyid] ),
                        fact_engagementprofile[tahunsurvey] < SELECTEDVALUE ( ref_calendar[year] ),
                        CROSSFILTER ( ref_calendar[year], fact_engagementprofile[tahunsurvey], NONE )
                    )
            ),
            CROSSFILTER ( ref_calendar[year], fact_engagementprofile[tahunsurvey], NONE )
        ),
        [MaxYear]
    )
RETURN
    CALCULATE (
        [% of favorable],
        fact_engagementprofile[id_companyid]
            IN VALUES ( fact_engagementprofile[id_companyid] ),
        fact_engagementprofile[tahunsurvey] IN a,
        CROSSFILTER ( ref_calendar[year], fact_engagementprofile[tahunsurvey], NONE )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[citrahemas_]

Wow it works, I'm very happy to know, thank you @v-xinruzhu-msft. But here there was a little problem when I applied it to real data, maybe because there was a lot of data so it was very heavy and not enough memory appeared. Can you simplify the dax, or are there any other suggestions for that?

[lbendlin]

You can use DAX Studio to look at the Query Plan and make modifications accordingly.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[citrahemas_]

Thank you for your advice @lbendlin, I've just added sample data, I hope it helps.