ALL function

[ttrandai]

Hello all, I am having a problem with my all function. To understand my problem, you should know my [fiscal half] column identifies which fiscal half the transaction was made Ex. FY18 1H or FY18 2H, and my [reporting period] contains the month and year the transaction took place. 

My goal is to write a DAX formula that is able to DISTINCTCOUNT the [fiscal half] with no filters on the [reporting period], while placing the DISTINCTCOUNT in an if statement that will output the correct Divider I need for a different equation.

When the report level filter has "FY18 1H" & "FY18 2H" selected, and the page level filter has [reporting period] selection of a single month the DAX formula outputs "6" when it should output 12. Shouldn't the all function clear filters on [reporting period] and result an output of "12"

Formula : Target Divider = if( CALCULATE( DISTINCTCOUNT( Table[Fiscal Half] ),all( [Reporting Period] ) )=1,6,12 )

[v-huizhn-msft]

Hi @ttrandai,

Yeah, ALL function ignores any filters that might have been applied. I create the following sample table.



Create measure use the formual:

DistinctCount = CALCULATE(DISTINCTCOUNT(Table1[physicians]),ALL(Table1[category]))

Add the Table1[category] as report level filter. Create a card visual, it display the measure value. Whenever I select any value in the page level, the measure value is constant 2.

 For your issue, it's abnormal, please check if there is other filter on Table[Fiscal Half]. Please feel free to ask if you have any other problem.

Best Regards,
Angelia

[ttrandai]

So I do have a filter on [Fiscal Half]. So imagine [Fiscal Half] has four choices FY17 1H, FY17 2H, FY18 1H, FY18 2H. I filter to where it is only FY18 1H and FY18 2H, does that effect the all function in any way?

In your case my [Fiscal Half] is your [Physicians] column.

Thanks in advance,

Tyler

[v-huizhn-msft]

Hi @ttrandai,

When I add Table1[physicians] as report level filter, I create the following formula. It still return 4 whenever you select any value.

DistinctCount = CALCULATE(DISTINCTCOUNT(Table1[physicians]),ALL(Table1[physicians]))

For you requriement, you need to create the formula.

result=CALCULATE( DISTINCTCOUNT( Table[Fiscal Half] ),all( [Fiscal Half] ) )

Best Regards,
Angelia

[ttrandai]

Essentially, this is very similar to my problem https://community.powerbi.com/t5/Desktop/ALL-function-ignored-inconsistent-functionality-when-also-u...

However I do not understand the solution.

[v-huizhn-msft]

Hi @ttrandai,

I totally understand you, but I can not open the link.

Best Regards,
Angelia

[ttrandai]

Hmm that's weird, well it's a topic here on this forum. Search "ALL() function ignored / inconsistent functionality when also using report level filter" and it should appear.

[v-huizhn-msft]

Hi @ttrandai,

Please update your Power BI desktop to the latest version, and check if it works fine.

Best Regards,
Aneglia

[ttrandai]

Hmm, I still can't seem to get the right number. Thanks for trying though!

[v-huizhn-msft]

Hi @ttrandai,

Do you mind share the .pbix file for further analysis?

Best Regards,
Angelia

[ttrandai]

I can't share the file because it is a private company file, however I realize that it might have something to do with my reporting period column format. Because in another situation I use the following formula

Forecast = CALCULATE(SUM(Table[Amount]),table[Scenario]="Forecast",all(table[Reporting Period]))

It displays the correct amount when "Reporting Period" filter is on select all however when I select a specific month it does not clear all "Reporting Period" filters and only calculates "Amount" for that certain month.

Note: I did split my column in the query, would that have any effect on the "Reporting Period" Column itself?