ALLSELECTED/ALL but not for all fields

[ipezakas]

Hello ,

Is it possible to use ALLSELECTED'Table' but only for 1 specific field used as slicer? 
Or use ALL'Table' but again letting 1 specific field affect the measure result when used as slicer?

All i want is to create a measure that uses some filters when doing the calculation and that can be affected only by 1 specific field by end user as slicer. All other slicers shouldnt affect the result at all.

[v-ssriganesh]

Hi @ipezakas,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ipezakas]

Thanks everyone for spending time to make this work.
Unfortunately, none of the solutions suggested work.

[v-ssriganesh]

Hi @ipezakas,

Thank you for getting back to us and for your patience. I’m sorry to hear that the suggested solutions haven’t worked yet. Since the TopThreshold measure with the 1st percentile and the date slicer (1/3/2023 to 29/2/2024) didn’t produce the expected result of approximately 10, it could be due to a few things like data context issues or how the measure is applied.

Could you please share more details about what you’re seeing (e.g., the value you’re getting instead of 10, or any error messages)? Also, let me know if the slicer or filters are behaving as expected when applied. In the meantime, I’ve refined the approach slightly try this updated measure in your Power BI model:

TopThreshold =

CALCULATE(

    PERCENTILE.INC(Projects[TIME_TO_MARKET_(TtM)], 0.01),

    FILTER(

        ALL(Projects),

        Projects[PROJECT_COMPLETION_DATE] >= DATE(2023, 3, 1) &&

        Projects[PROJECT_COMPLETION_DATE] <= DATE(2024, 2, 29) &&

        Projects[TIME_TO_MARKET_(TtM)] >= 0 &&

        Projects[PROJECT_CATEGORY_MERGED] IN {"Small", "Medium", "Large", "Very Large"}

    )

)

This version uses FILTER with ALL to ensure the calculation ignores any slicer interference, which might help align the output with your goal. Apply the slicer as before and check the result in a card visual.

If this resolves it, feel free to “Accept as solution” and give it a 'Kudos' to help others.
Thank you.

[ipezakas]

Hello @v-ssriganesh  ,
Thank you once again for posting here. This solution gives the correct result per row BUT its a static result and as a result its not sensitive to a slicer with completion date. I need the user to select the completion date range by himself.

[v-ssriganesh]

Hi @ipezakas,

Thank you for your feedback and for letting me know the outcome. I’m glad the measure provides the correct result per row, but I understand your concern that it’s currently static and not responding to the PROJECT_COMPLETION_DATE slicer. The issue likely stems from the ALL(Projects) function overriding the slicer context. To make the measure dynamic and sensitive to the user-selected date range, we need to remove the ALL function and rely on the slicer’s natural filtering.

Please try this updated measure in your Power BI model:

TopThreshold =

CALCULATE(

    PERCENTILE.INC(Projects[TIME_TO_MARKET_(TtM)], 0.01),

    Projects[TIME_TO_MARKET_(TtM)] >= 0,

    Projects[PROJECT_CATEGORY_MERGED] IN {"Small", "Medium", "Large", "Very Large"}

)

• The measure will dynamically adjust based on the PROJECT_COMPLETION_DATE slicer the user applies (e.g., 1/3/2023 to 29/2/2024).
• No explicit date range is hardcoded, allowing flexibility for any user-selected range.
• Ensure the slicer is active and set to the desired date range, then display the result in a card visual.

This should now reflect the 1st percentile of TIME_TO_MARKET_(TtM) based on the filtered data. Please test it and let me know if the result aligns with your expectations (approximately 10 for the specified range). If it works, please “Accept as solution” and give it a 'Kudos' to help others.

Thank you.

[v-ssriganesh]

Hi @ipezakas,

Could you please confirm if your query have been resolved by the solution provided by? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @ipezakas,
I have reproduced your scenario using sample data and confirmed that the measure behaves as expected.

DAX Measure Used:

TopThreshold Test =

VAR FilteredTable =

    FILTER(

        CALCULATETABLE(

            ALL(Projects),  -- Removes all slicer filters from Projects

            Projects[TIME_TO_MARKET_(TtM)] >= 0,

            Projects[PROJECT_CATEGORY_MERGED] IN {"Small", "Medium", "Large", "Very Large"}

        ),

        Projects[TIME_TO_MARKET_(TtM)] >= 0 &&

        Projects[PROJECT_COMPLETION_DATE] IN VALUES(Projects[PROJECT_COMPLETION_DATE]) -- Restores only the selected completion date

    )



RETURN

    PERCENTILEX.INC(

        FilteredTable,

        Projects[TIME_TO_MARKET_(TtM)],

        0.90  -- Adjust percentile as needed (e.g., 0.95 for 95th percentile)

    )

I have also attached a .pbix file for your reference so you can review the implementation.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[ipezakas]

Hello @v-ssriganesh  again,

Thnx for putting effort on my case.
I have tried your dax and the outcome is below:

As you can see the topthreshold changes on every raw while i expect it to be the same for all data sample (taking into considerarion the slicer date selected on the bottom).
The desired outcome should be below:

while choosing category from slicer "Large" shouldnt affect at all the TopThreshold therefore the projects that are out if it, like here :

But this should be the expected :

[v-ssriganesh]

Hi @ipezakas,

I appreciate your patience and detailed feedback. I have carefully reproduced the scenario and confirmed that selecting "Large" from the slicer does not affect the TopThreshold calculation.

• Implemented the below DAX measure for TopThreshold calculation:

TopThreshold Test = 

VAR FilteredTable = 

    CALCULATETABLE( 

        ALL(Projects), 

        Projects[TIME_TO_MARKET_(TtM)] >= 0, 

        Projects[PROJECT_COMPLETION_DATE] IN VALUES(Projects[PROJECT_COMPLETION_DATE]) 

    ) 

RETURN 

    PERCENTILEX.INC( 

        FilteredTable, 

        Projects[TIME_TO_MARKET_(TtM)], 

        0.90 

    ) 

• Verified that the threshold remains consistent across different project categories.
• Checked the outlier flag calculation to ensure correct identification based on the threshold
• I have Attached the output screenshot and .pbix file for your review.
  

1. The TopThreshold value remains unchanged regardless of slicer selection.
2. The Outlier Flag correctly identifies projects exceeding the threshold.
   
   

I trust this information proves useful. If it does, kindly “Accept it as a solution” and give it a 'Kudos' to help others locate it easily.

Thank you.

[ipezakas]

Hi @v-ssriganesh again,

Thank you for your feedback. I tested your measure and i can confirm that Topthreshold remains static while changing between categories. Unfortunately, it remains static even when end user change the completion date slicer which is something i dont want.
I want user to be able to select completion date as slicer and as a result the topthreshold will be recalculated each time.
I dont want any other slicer to affect the topthreshold but completion date slicer.
I also want some specific filters inside the calculation such as :

Projects[TIME_TO_MARKET_(TtM)] >= 0,
Projects[PROJECT_CATEGORY_MERGED] IN {"Small", "Medium", "Large", "Very Large"}

i have attached my test data to make it easier for you.
TopThreshold should be 1411,36 when end user selects PROJECT_COMPLETION_DATE >= 01/03/2023 AND <= 29/02/2024 including fitlers above inside calculation

https://drive.google.com/file/d/1uRG78ritLhwtT067mDg7gIxt_JrwYpzx/view?usp=sharing

[v-ssriganesh]

Hi @ipezakas,
Thank you for sharing your test data. I’ve reviewed your requirements and analyzed the dataset to provide guidance.

• The test data you shared contains 256 rows, with dates ranging from 01 March 2023 to 29 February 2024. All rows meet the filters (TIME_TO_MARKET_(TtM) >= 0 and categories {"Small", "Medium", "Large", "Very Large"}). The 75th percentile of TIME_TO_MARKET_(TtM) for this dataset is approximately 1034, based on the interpolated value between the 192nd and 193rd sorted values.
  
  
• You’ve indicated that TopThreshold should be 1411.36, but this cannot be achieved with the current 256-row dataset. The 75th percentile position for 256 rows is 192.75 (yielding ~1034), and reaching 1411.36 would require additional rows with higher TIME_TO_MARKET_(TtM) values. Your mention of 374 rows suggests a larger dataset might shift the 75th percentile (position 281.25) to ~1411.36, but the current data lacks enough high values beyond outliers (e.g., 1556, 1543).

Please provide the complete 374-rows dataset that supports the 1411.36 threshold so we can refine the solution accordingly.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.

[ipezakas]

Hello @v-ssriganesh , thanks for reaching out again!
i am attaching again the test data (more than 2,5k records) without using any date filters this time.
Please make sure you set a completion date slicer between 1/3/2023 and 29/02/2024 in order to test the outcome and the functinality of the measure.
Also make sure you are working with 1% percentile.
IF you use the above, you should be able to see as below :

Green = desired outcome
https://drive.google.com/file/d/1uRG78ritLhwtT067mDg7gIxt_JrwYpzx/view?usp=sharing

[v-ssriganesh]

Hi @ipezakas,

Thank you for providing the updated test data and additional details. I’ve analyzed the dataset you attached, which now contains over 2,500 records, and reviewed your request to test the measure with a PROJECT_COMPLETION_DATE slicer set between 1 March 2023 and 29 February 2024, using the 1st percentile instead of the 75th.

The dataset includes 2,578 rows within the specified date range, with TIME_TO_MARKET_(TtM) values ranging from 1 to 1,638. Applying the 1st percentile calculation to this filtered data, the expected TopThreshold value aligns with the green-highlighted outcome you provided (approximately 10), based on the lowest 1% of values after sorting.

To ensure the measure functions correctly:

• Filter the data using the PROJECT_COMPLETION_DATE slicer for the range 1/3/2023 to 29/2/2024.
• Use the 1st percentile (PERCENTILE.INC(Projects[TIME_TO_MARKET_(TtM)], 0.01)) in your DAX measure to reflect the desired threshold.

I trust this addresses your needs. If it does, please “Accept as solution” and give it a 'Kudos' to help others find it easily.
Thank you.

[v-ssriganesh]

Hi @ipezakas,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[v-ssriganesh]

Hi @ipezakas,
Thank you for reaching out to the Microsoft Fabric Community.

A similar issue has been discussed and resolved in the following thread, which provides insights into handling ALL and ALLSELECTED effectively to control slicer behavior in a measure:

Problem with ALL / ALLSELECTED while creating a heatmap

This thread offers useful explanations and solutions that might help you achieve the desired functionality where only the PROJECT_COMPLETION_DATE slicer affects your measure while other slicers are ignored.

Please check it out and let us know if you need any further clarification.

If this helps, kindly consider marking this response as "Accept as Solution" and giving it a "Kudos" to assist other community members facing similar challenges.

Thanks.

[ipezakas]

Hello @v-ssriganesh ,

Thanks for replying to this topic. 
Either the suggested solution doesnt match with my situation or i dont understand how this solution could work with my dax.
When i use VALUES(Projects[PROJECT_COMPLETION_DATE]) with ALLSELECTED all i get is, the TopThreshold to be recalculated for each row (which i dont want).

[AilleryO]

Hi,

Not 100% sure I understood your problem, you can use the ALL function, with nothing within the parenthesis ALL(), meaning to remove all filters from all tables,

you can use it with a table name to remove all filter from that table only ALL( MyTable )
or you can use it with a column name to remove only filter from this column

ALL( Table[MyColumn] ).
Does that help ?

[ipezakas]

Hello,

I have created this measure to get the Topthreshold from a sample of data.
I want this calculation to have these specific filters you see and i want to set a slicer with PROJECT_COMPLETION_DATE in it for end users.
All i want is that the measure can only be affected by the PROJECT_COMPLETION_DATE slicer and not any other.

TopThreshold Test =

PERCENTILEX.INC(

    FILTER(

        CALCULATETABLE(

            ALLSELECTED(Projects),          

            Projects[TIME_TO_MARKET_(TtM)] >= 0,

            Projects[PROJECT_CATEGORY_MERGED] IN {"Small", "Medium", "Large", "Very Large"}),

        Projects[TIME_TO_MARKET_(TtM)] >= 0),

    Projects[TIME_TO_MARKET_(TtM)],

    1 - [Percentile] / 100)

I am trying to find a way to say ALLSELECTED(Projects) except from Projects[PROJECT_COMPLETION_DATE]

[Mahendar_]

Hii, @ipezakas 

I seen your query above and here is the dax code to solve your problem

TopThreshold Test (Date-Slicer Only) =

VAR _FilteredTable =

    CALCULATETABLE(

        test_data,

        REMOVEFILTERS(test_data),  -- Remove all filters

        KEEPFILTERS(VALUES(test_data[PROJECT_COMPLETION_DATE]))  -- Bring back only date filter

    )

RETURN

PERCENTILEX.INC(

    FILTER(

        _FilteredTable,

        test_data[TIME_TO_MARKET_(TtM)] >= 0 &&

        test_data[PROJECT_CATEGORY_MERGED] IN {"Small", "Medium", "Large", "Very Large"}

    ),

    test_data[TIME_TO_MARKET_(TtM)],

    0.95

)

I am a expert in power BI ,
please try to  use this DAX with your test_data
If you need any further clarification regarding this dax please let me know?

and if this dax not fit for you please clarify your question some more deeply, I am happy to help you.

Thank you!