Help with ignoring filters on count of all rows

[EpicTriffid]

Hi all,

This is probably incredibly simple to solve but I must be having a bad day or something.

Situation:

I am pulling in 4 tables from my database (although this query only currently concerns 2 of the 4):

StaffTotals: My main table with a unique row for each memeber of staff in each year. 

Units: A table with multiple rows for each person in each year.

These are joined via a single direction many to one relationship, with StaffTotals filtering my units table. The related column is Entry_ID, which is their staff ID number and the related academic year (e.g. 12345678_2021/22) This means I can aggregate values for people from the units table by year.

Across the report I have three main filters:

StaffTotals[Academic Year]: I use this omit certain years of data. In this case, as we are planning ahead, I have filtered out any records with "2022/23" beside them.

StaffTotals[Department]: This filters out some departments that aren't relevant currently. 

StaffTotals[Omit Flag]: This filters out some people that are not relevant currently.

Possibly Related:

I also have a datetable connected by a many to many relationship that converts academic years to real years by selecting the highest year, lowest year, and fills in the gaps to make 365 rows for each academic year.

Problem:

I am trying to get a total count of rows in the Units table, for the purpose of getting a general overview of how many rows the database has. As I will be doing this for the other tables, I am using DateTable[Academic Year] in my matrix so that it can work across all tables and not be affected by the StaffTotals[Academic Year] filter. 

My understanding is that I can use ALL in a CALCULATE statement to choose which fields to filter out. So I'm currently using the below code:

DB Unit Records = 
CALCULATE(
    COUNTROWS(Units),
    ALL(StaffTotals[Academic Year]),
    ALL(StaffTotals[Omit Flag]),
    ALL(StaffTotals[Department]))

However, the totals I get are like the below:

Academic Year	DB Unit Records
2018/19	3295
2019/20	3763
2020/21	5280
2021/22	5245
2022/23	4338
Total	21958

The totals that I should be seeing:

Academic Year	DB Unit Records
2018/19	3295
2019/20	3763
2020/21	5280
2021/22	5267
2022/23	4353
Total	21958

So, only the last two years have different numbers, but the first three years is total is correct in both. I get the correct number by manually removing all the filters across the report, but the problem is that I can't seem to simulate a condition of no filters within a measure. Any help would be appreciated.

[v-luwang-msft · Accepted solution]

Hi @EpicTriffid ,

Try to use the below:

DB Unit Records = 
CALCULATE(
    COUNTROWS(Units),
    ALLEXCEPT(StaffTotals,StaffTotals[Academic Year]))
    

or 

DB Unit Records = 
CALCULATE(
    COUNTROWS(Units),
    filter(all(StaffTotals),StaffTotals[Academic Year])=max(StaffTotals[Academic Year]))
    

refer: https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

And if question still not solved ,could you pls share your pbix file  and remember to remove confidential data.

Best Regards

Lucien