DAX Measure with multiple IF conditions

Question

I'm hopping someone might be able to help me out as I'm not sure how to get this DAX measure to work. I have the following data:

Date    Category
15.2.23  A
15.2.23  A
15.2.23  A
16.2.23  B
16.2.23  C
17.2.23  A
17.2.23  B
20.2.23  C

I want the DAX measure to count all A items for a specific date and return 0 if there is no A item for a date in the table :

Example :

3 for 15.2.23
0 for 16.2.23
1 for 17.2.23
0 for 20.2.23

I've tried all different combinations and I still can get it to work - I believe this has something to do with my model DATE Table as this includes all dates in a year and not only the dates in my table.

DATA Table 
15.2.23   
16.2.23    
17.2.23  
18.2.23
19.2.23 
20.2.23  

ALL OOS AD Table 
15.2.23  A
15.2.23  A
15.2.23  A
16.2.23  B
16.2.23  C
17.2.23  A
17.2.23  B
20.2.23  C

DAX formulas I used and they don't return the correct values:

Total OOS A (NS)* = 
CALCULATE (COUNT('All_OOS_AD'[Date]),('All_OOS_AD'[Item Category]= "A")) 



Total OOS A (NS)* = 
CALCULATE (
    IF (
        COUNTROWS (
            FILTER (
                All_OOS_AD,
                All_OOS_AD[Item Category] = "A" &&
                NOT(ISBLANK(All_OOS_AD[Date])) &&
                WEEKDAY(All_OOS_AD[Date]) <> 1 && WEEKDAY(All_OOS_AD[Date]) <> 7
            )
        ) > 0,
        COUNTROWS (
            FILTER (
                All_OOS_AD,
                All_OOS_AD[Item Category] = "A" &&
                NOT(ISBLANK(All_OOS_AD[Date])) &&
                WEEKDAY(All_OOS_AD[Date]) <> 1 && WEEKDAY(All_OOS_AD[Date]) <> 7
            )
        ),
        0
    )
)



Total OOS A (NS)* = 
CALCULATE (
    IF (
        COUNTROWS ( FILTER ( All_OOS_AD, All_OOS_AD[Item Category] = "A" && NOT(ISBLANK(All_OOS_AD[Date]))) ) > 0,
        COUNTROWS ( FILTER ( All_OOS_AD, All_OOS_AD[Item Category] = "A" && NOT(ISBLANK(All_OOS_AD[Date]))) ),
        0
    )
)



Total OOS A (NS)* = 
CALCULATE (
    IF (
        COUNTROWS ( FILTER ( All_OOS_AD, All_OOS_AD[Item Category] = "A" && NOT(ISBLANK(All_OOS_AD[Date]))) ) > 0,
        COUNTROWS ( FILTER ( All_OOS_AD, All_OOS_AD[Item Category] = "A" && NOT(ISBLANK(All_OOS_AD[Date]))) ),
        0
    )
)



Total OOS A (NS)* =
CALCULATE (
    COUNTAX(
        VALUES('YourTable'[Date]),
        IF (
            COUNTROWS(FILTER('YourTable', 'YourTable'[Item Category] = "A" && 'YourTable'[Date] = VALUES('YourTable'[Date]))) > 0,
            1,
            0
        )
    )
)


Total OOS A (NS)* = 
CALCULATE (
    COUNTAX(
        VALUES(All_OOS_AD[Date]),
        IF (
            COUNTROWS(FILTER(All_OOS_AD, All_OOS_AD[Item Category] = "A" && All_OOS_AD[Date] = VALUES(All_OOS_AD[Date]))) > 0,
            1,
            0
        )
    )
)


Total OOS A (NS)*
CALCULATE (
    IF (
        COUNTROWS (
            FILTER (
                ALL('YourTable'[Date]),
                CALCULATE (
                    COUNTROWS (
                        FILTER (
                            'YourTable',
                            'YourTable'[Item Category] = "A" && 'YourTable'[Date] = 'YourTable'[Date]
                        )
                    ) > 0
                )
            )
        ) > 0,
        CALCULATE (
            COUNTROWS (
                FILTER (
                    'YourTable',
                    'YourTable'[Item Category] = "A"
                )
            )
        ),
        0
    )
)

Solution:

Answer 1

You were trying to directly filter and count within the All_OOS_AD table without adequately ensuring that every date from the DATA Table was considered. This means that if a date did not have any "A" items, it might not be included in the result set or would not explicitly return a 0 count as intended.

Total OOS A (NS) = 
CALCULATE (
    COUNTROWS(FILTER(All_OOS_AD, All_OOS_AD[Item Category] = "A")),
    ALL('DATA Table'), // verify if the calculation is done over all dates
    VALUES('DATA Table'[Date]) // This forces evaluation for each date in your date table
)

---

Update :

Try to create a calendar date and add the relationship between your All_OOS_AD and the calendar date:

Date = 
CALENDAR("2023-01-01", "2023-01-31")

Then :

CountAItemsForDate = 
SUMX(
    VALUES(All_OOS_AD[Date]), 
    VAR currentDate = [Date]
    RETURN IF(
        CONTAINS(All_OOS_AD, All_OOS_AD[Date], currentDate, All_OOS_AD[Category], "A"),
        CALCULATE(COUNTROWS(All_OOS_AD), All_OOS_AD[Category] = "A", All_OOS_AD[Date] = currentDate),
        0
    )
)