Count of failures per year?

[Anonymous]

Hello Im new to DAX formulas, Im hoping someone can help me with my below task

I have imported data from an excel file that contained 3 work sheets named "2022", "2023" and "2024".

Each row in these work sheets represents a failure

I would like to create a summary table that shows the Total count of failures per year.

How do I state this in DAX? 

[Anonymous · Accepted solution]

Hi @Anonymous 

Based in your needs, I have created the following tables:

Then please try the following dax to create a new table:

SummaryTable = 
SUMMARIZE(
    UNION(
        SELECTCOLUMNS('2022', "Year", 2022, "Failure", '2022'[Failure]),
        SELECTCOLUMNS('2023', "Year", 2023, "Failure", '2023'[Failure]),
        SELECTCOLUMNS('2024', "Year", 2024, "Failure", '2024'[Failure])
    ),
    [Year],
    "Total Failures", COUNTROWS(
        FILTER(
            UNION(
                SELECTCOLUMNS('2022', "Year", 2022, "Failure", '2022'[Failure]),
                SELECTCOLUMNS('2023', "Year", 2023, "Failure", '2023'[Failure]),
                SELECTCOLUMNS('2024', "Year", 2024, "Failure", '2024'[Failure])
            ),
            [Year] = EARLIER([Year])
        )
    )
)

Result:

Best Regards,

Jayleny

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...