Forum Discussion

jozsacruz's avatar
jozsacruz
Frequent Visitor
5 years ago
Solved

Counting a value in each column by week

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!


Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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:

    12.15.4.1.PNG

    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

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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:

    12.15.4.1.PNG

    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