Counting a value in each column by week

[jozsacruz]

Hello, 

I have a column that says either pass or fail and I 'm tyring to do a countif to let me know how many pass or fails I have but I want to filter it by week. I have a similar DAX formula where using allexcept(table name, name column) worked but when I try it with again, it says that W12 it says "Cannot convert value 'W12' of type Text to type True/False." 

Here is the formula that I was able to use to get a weekly value:

Column 1 = calculate(average('Table Name'[Column name ]), allexcept('TableName','TableName'[Week]))

Here's what I have so far:

Column 1 = CALCULATE(COUNT('TableName'[ColumnName]),FILTER('TableName','TableName'[ColumnName]="pass"),FILTER('TableName','TableName'[Week]))

Thank you!

[v-eqin-msft · Accepted solution]

Don't @jozsacruz,

I did it in two ways, please try:

Measure =
CALCULATE (
    COUNT ( TableName[ColumnName] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Week] ),
        'TableName'[ColumnName] = "Pass"
    )
) + 0

O

Measure 2 =
CALCULATE (
    COUNT ( TableName[ColumnName] ),
    FILTER (
        ALL ( 'TableName' ),
        'TableName'[ColumnName] = "Pass"
            && 'TableName'[Week] = MAX ( 'TableName'[Week] )
    )
) + 0

The final output is shown below:

Here's the pbix file.

If this post helps, then consider Accept it as the solution to help other members find it faster.

Best regards
Eyelyn Qin

[Ashish_Mathur]

Hi,

This is what you should do:

1. Create a Calendar Table and extract Year, Month Name, Month number and week number is seperate columns using calculated column formulas
2. Create a relationship from the Date column in your dataset to the Date column in your Calendar Table
3. Create slicers for Year, Month Name and Week from the Calendar Table and select one entry in each slicer
4. To your visual, drag "ColumnName" column
5. Write this measure

Measure = countrows(data)

Hope this helps.