topic Re: URGENT!! Removing filter context from a measure in DAX Commands and Tips

URGENT!! Removing filter context from a measure

Anonymous — Sat, 09 Mar 2024 07:40:01 GMT

Hello Power BI community!

can you modify this dax code such that even if 'df_Search Order (First)'[Search Order (First)] column is filtered I should always get a total count for Search Order in TotalSum_Category_Occasion_SingleSelect measure? It shouldn't become equal to the DistinctCount_Category_Occasions_SingleSelect measure when I filter the table for a search order...

TotalSum_Category_Occasion_SingleSelect measure:

TotalSum_Category_Occasion_SingleSelect = VAR BrandsSelection = ISFILTERED('df_brands'[Brands]) VAR TotalCount = IF ( BrandsSelection, CALCULATE( SUMX( VALUES('Respondent'[Unique_ID]), [TotalDistinctCount_Category_Occasions_SingleSelect] ), 'df_brands'[value] = "Yes", ALLSELECTED('Respondent') ), CALCULATE( SUMX( VALUES('Respondent'[Unique_ID]), [TotalDistinctCount_Category_Occasions_SingleSelect] ), 'df_Brands'[value] IN {"Yes", "No", "", "Null"}, ALLSELECTED('Respondent') )) RETURN TotalCount

DistinctCount_Category_Occasions_SingleSelect measure:

DistinctCount_Category_Occasions_SingleSelect = VAR BrandsSelection = ISFILTERED('df_brands'[Brands]) VAR TotalCount = IF ( BrandsSelection, CALCULATE( DISTINCTCOUNT('Respondent'[Unique_ID]), 'df_Occasions'[value1] = "Yes", 'df_Category Purchased'[value] = "Yes", 'df_brands'[value] = "Yes", FILTER( 'Respondent', 'Respondent'[Gender_blended] = SELECTEDVALUE(Respondent[Gender_blended]) || 'Respondent'[country] = SELECTEDVALUE(Respondent[country]) || 'Respondent'[age_group] = SELECTEDVALUE(Respondent[age_group]) || RELATED('df_Brand Planning'[Brand_Planning]) = SELECTEDVALUE('df_Brand Planning'[Brand_Planning]) || RELATED('df_Category Planning'[Category_Planning]) = SELECTEDVALUE('df_Category Planning'[Category_Planning]) || RELATED('df_Search Order (First)'[Search Order (First)]) = SELECTEDVALUE('df_Search Order (First)'[Search Order (First)]) || RELATED('df_Shopping Mission (Combined)'[Shopping_Mission_Combined]) = SELECTEDVALUE('df_Shopping Mission (Combined)'[Shopping_Mission_Combined]) )), CALCULATE( DISTINCTCOUNT('Respondent'[Unique_ID]), 'df_Occasions'[value1] = "Yes", 'df_Category Purchased'[value] = "Yes", 'df_Brands'[value] IN {"Yes", "No", "", "Null"}, FILTER( 'Respondent', 'Respondent'[Gender_blended] = SELECTEDVALUE(Respondent[Gender_blended]) || 'Respondent'[country] = SELECTEDVALUE(Respondent[country]) || 'Respondent'[age_group] = SELECTEDVALUE(Respondent[age_group]) || RELATED('df_Brand Planning'[Brand_Planning]) = SELECTEDVALUE('df_Brand Planning'[Brand_Planning]) || RELATED('df_Category Planning'[Category_Planning]) = SELECTEDVALUE('df_Category Planning'[Category_Planning]) || RELATED('df_Search Order (First)'[Search Order (First)]) = SELECTEDVALUE('df_Search Order (First)'[Search Order (First)]) || RELATED('df_Shopping Mission (Combined)'[Shopping_Mission_Combined]) = SELECTEDVALUE('df_Shopping Mission (Combined)'[Shopping_Mission_Combined]) ))) RETURN TotalCount

As you see below the % calculated by dividing this two measures which equals 100% if I filter any search order..

Category_Occasion_SingleSelect% = [DistinctCount_Category_Occasions_SingleSelect]/ [TotalSum_Category_Occasion_SingleSelect]

What is happening right now:

Expected Output:

@MFelix , @amitchandak , @Ashish_Mathur

Re: URGENT!! Removing filter context from a measure

lbendlin — Sat, 09 Mar 2024 15:52:49 GMT

This here is a forum where users help users, time permitting. For urgent requests contact a Microsoft partner near you.

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: URGENT!! Removing filter context from a measure

Anonymous — Mon, 11 Mar 2024 06:57:14 GMT

hello lbendlin

Thanks!

Here is the sample pbix: https://drive.google.com/file/d/11FppQjqX3oQEEcPuwStBxAYBdY66i-R-/view?usp=sharing

Please take a look and share any solution you might have.

Re: URGENT!! Removing filter context from a measure

lbendlin — Mon, 11 Mar 2024 20:37:48 GMT

Do not include anything not related to the issue or question. Your sample file is way too big.

Re: URGENT!! Removing filter context from a measure

Anonymous — Tue, 12 Mar 2024 04:30:57 GMT

lbendlin The dataset itself is huge and has many connections. Can you please take a look at the issue?

Re: URGENT!! Removing filter context from a measure

lbendlin — Tue, 12 Mar 2024 11:36:13 GMT

I can only assist if you can provide sample data that covers the issue without unrelated items. I hope someone else can help you further.