Distinctcount of Individuals in a Department Not Returning Expected Result

[ExcelMonke]

Hello,

I am currently running into an issue where my DistinctCount Measure is not returning the expected result. I am also unable to figure out where it is going wrong, even after diving into it in DAX Studio.
Goal:
Count distinct number of individuals in a department any given week

Current Result (Test Column):

Expected Result:
The expected result should be 2 across the rows

This is my current DAX for the "TEST" Measure:

CALCULATE (
    DISTINCTCOUNT ( NameTable[Name] ),
    ALLEXCEPT ( 'Location Dim', 'Location Dim'[DepartmentName] ),
    ALLEXCEPT('Date Dim','Date Dim'[WeekBeginningDT])
)

Current DAX within Studio:

DEFINE
MEASURE 'Measure Table'[TEST] = CALCULATE (
    DISTINCTCOUNT ( NameTable[Name] ),
    ALLEXCEPT ( 'Location Dim', 'Location Dim'[DepartmentName] ),
    ALLEXCEPT('Date Dim','Date Dim'[WeekBeginningDT])
)
EVALUATE
FILTER(
SUMMARIZECOLUMNS(
    'Date Dim'[WeekBeginningDT],
    'Location Dim'[DepartmentName],
    NameTable[SpecialNames],
    KEEPFILTERS( FILTER( ALL( 'Date Dim'[WeekBeginningDT] ), 'Date Dim'[WeekBeginningDT] >= DATE(2023,12,10) && 'Date Dim'[WeekBeginningDT] <= DATE(2024,2,25) )),
    KEEPFILTERS( TREATAS( {"XXX"}, 'Location Dim'[DepartmentName] )),
    KEEPFILTERS( TREATAS( {"YYY"}, NameTable[YYY] )),
    KEEPFILTERS( TREATAS( {"Y"}, NameTable[SpecialNames] )),
    "Hours", [Hours],
    "TEST", [TEST]
)
,[Hours] > 0
)
ORDER BY 
    'Date Dim'[WeekBeginningDT] ASC,
    'Location Dim'[DepartmentName] ASC,
    NameTable[SpecialNames] ASC

I added in the filters to see if this was a filter issue. The filters listed in the DAX Studio code are the same as my page level filters in the PBI Desktop

Regardless of whether the measure is built in DAX Studio or within PBI Desktop Directly, I keep getting a result that is not expected.

Any help is appreciated!

[ExcelMonke]

thank you for the response. The expected value should just be 2, rather than 4578, because during this time period there are only 2 individuals in that department.

[Anonymous]

Hi @amitchandak ,thanks for the quick reply, you are correct, and I add to the list of possible causes.

Hi @ExcelMonke ,

Regarding your question, there are three tables in total.
Fact table: NameTable
Dimension table: Location Dim , Date Dim

1. I've assumed some data :

2. Ensure that there is an active one-to-many relationship between the fact table and the dimension table. Incorrect model relationships will affect the delivery of filters, details of which can be found below:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

3. The problem may be with your original dax expression. Modify your dax expression as amitchandak provides:

Measure = CALCULATE(DISTINCTCOUNT(NameTable[Name]),ALLEXCEPT('NameTable','Date Dim'[WeekBeginningDT],'Location Dim'[DepartmentName]))

4. Final output:

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@ExcelMonke , what is expected output, the below will ignore all filter other than Department and WeekBegining date

CALCULATE (
DISTINCTCOUNT ( NameTable[Name] ),
ALLEXCEPT ( 'NameTable', 'Location Dim'[DepartmentName] ,'Date Dim'[WeekBeginningDT])
)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[ExcelMonke]

Thank you for the reply! If the NameTable is not the main fact table, would I have to change the ALLEXCEPT table to the fact table or can I use the NameTable?