DAX calculation - Calculated field is not working while multiple selections in the dropdown

Question

I have a problem while selecting multiple years in the dropdown. I have 2 DAX measure calculations which is working fine while one selection in the dropdown, however, it provides blank values while multiple selections in the dropdown. How can I fix this issue? Here is my DAX calculation -

                    COUNT(table[id]),  
                     FILTER(ALLEXCEPT(table, table[office]),
                     table[Fiscal_Year] < SELECTEDVALUE(table[Fiscal_Year]) &&
                     (table[Fiscal_Year_Closed] = blank() ||
                     table[Fiscal_Year_Closed] >= SELECTEDVALUE(table[Fiscal_Year])
                     )))

what changes do I need to make to work it for multiple selections?

Answer 1

When working with multiple selections in a dropdown for a DAX measure in Power BI, the function SELECTEDVALUE might not work as expected, since it is designed to return a single value. When multiple values are selected, SELECTEDVALUE returns a blank.

Cal = CALCULATE(
        COUNT(table[id]),
        FILTER(
            ALLEXCEPT(table, table[office]),
            table[Fiscal_Year] IN VALUES(table[Fiscal_Year]) &&
            (table[Fiscal_Year_Closed] = BLANK() ||
             table[Fiscal_Year_Closed] >= MAX(table[Fiscal_Year]))
        )
)

Answer 2

You will need to replace SELECTEDVALUE with either MIN or MAX with a range conditional if that is what is needed. Eg MIN((table[Fiscal_Year]) <= x && x <= MAX((table[Fiscal_Year]).

SELECTEDVALUE only returns one unique value and if more than one value is found it returns BLANK().