topic Re: ALLEXCEPT - how to respect the date column filter within a matrix visual in DAX Commands and Tips

ALLEXCEPT - how to respect the date column filter within a matrix visual

Anonymous — Fri, 09 Feb 2024 18:41:43 GMT

I have this measure - which when applied to a single date works fine. I want to be able to compare the percent of total for the date column. Currently when I add another date to the visual using the slicer, it recalculates the total as all dates included in the visual.

1 Date (correct percentages)

2 dates: percent of whole is calculated with all dates instead of for the individual dates.

# Portfolio % = DIVIDE( [# Total_Market_Value_Base], CALCULATE( SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]), ALLSELECTED(FactPortfolioSectorDetails) ) )

I tried to use ALLEXCEPT instead and it just calculates 100% for every category.

# TEST Portfolio % = DIVIDE( [# Total_Market_Value_Base], CALCULATE( SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]), ALLEXCEPT(DimCalendar,DimCalendar[Date]), ALLEXCEPT(DimSector, DimSector[Sector], DimSector[Subcategory]) ) )

I also tried having my ALLSELECTED filter, then adding the ALLEXCEPT on top of it and got the same result as my original measure.

# TEST Portfolio % = DIVIDE( [# Total_Market_Value_Base], CALCULATE( SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]), ALLSELECTED(FactPortfolioSectorDetails), ALLEXCEPT(DimCalendar,DimCalendar[Date]) ) )

Thank you in advance.

Re: ALLEXCEPT - how to respect the date column filter within a matrix visual

lbendlin — Sat, 10 Feb 2024 21:25:29 GMT

You may want to consider using REMOVEFILTERS instead.

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: ALLEXCEPT - how to respect the date column filter within a matrix visual

talespin — Sun, 11 Feb 2024 15:12:43 GMT

hi Anonymous ,

I am using AdventureWorks database. I have a Date Table and Fact Table linked on Order date.

[MSalesAmount] is nothing but SUM(SalesAmount).

Measure

----------

Percent Of Total =

VAR _Total = CALCULATE([MSalesAmount], REMOVEFILTERS(DimProductCategory[EnglishProductCategoryName]) )

RETURN DIVIDE([MSalesAmount], _Total)

Re: ALLEXCEPT - how to respect the date column filter within a matrix visual

Anonymous — Mon, 12 Feb 2024 18:50:06 GMT

talespin lbendlin , I used REMOVEFILTERS instead and got my intended results, thanks very much.

Re: ALLEXCEPT - how to respect the date column filter within a matrix visual

talespin — Tue, 13 Feb 2024 04:19:06 GMT

hi Anonymous

You're welcome.