PowerBI - calculate amount of rows depending on values in multiple columns

2
votes

PowerBi Desktop v 2.64.5285.741 64-bit (November 2018)

Hello,

Imagine I have a table called WorkReport

enter image description here

In PowerBi Desktop, what would be the correct DAX syntax for finding the amount of rows that contains both Status = 'Not Started' and Progress = "slow"?

Below I will provide a list of queries that I've unsuccessfully tried:

report = CALCULATE(COUNTAX(WorkReport,WorkReport[Status]), WorkReport[Status] = "Not Started") && WorkReport[progress]WorkReport[progress], WorkReport[progress] = "slow")

report = CALCULATE(COUNT(WorkReport,WorkReport[Status]), WorkReport[Status] = "Not Started") && WorkReport[progress]WorkReport[progress], WorkReport[progress] = "slow")

report = CALCULATE(COUNTAX(WorkReport,WorkReport[Status]), WorkReport[Status] = "Not Started") AND WorkReport[progress]WorkReport[progress], WorkReport[progress] = "slow")

report = CALCULATE(COUNT(WorkReport,WorkReport[Status]), WorkReport[Status] = "Not Started") AND WorkReport[progress]WorkReport[progress], WorkReport[progress] = "slow")

report = COUNTROWS(FILTER(WorkReport,WorkReport[Status]="NotStarted") AND (WorkReport, WorkReport[progress] = "slow"))

report = COUNTROWS(FILTER(WorkReport,WorkReport[Status]="NotStarted") && (WorkReport, WorkReport[progress] = "slow"))

enter image description here enter image description here

Please advise if you were able to achieve this in PowerBI Desktop tool. Thanks!

2
functionpowerbidax

2 Answers

3
votes

The measure itself is a simple COUNTROWS, but you need to use CALCULATE to transition the context, applying the filters you want:

MyMeasure = 
    CALCULATE ( 
        COUNTROWS ( WorkReport ),
        WorkReport[Status] = "Not Started",
        WorkReport[Progress] = "slow"
    )
2
votes

You're on the right track, but your syntax is a bit off. Try this one:

report =
COUNTROWS(
    FILTER(
        WorkReport,
        WorkReport[Status] = "NotStarted" &&
        WorkReport[progress] = "slow"
    )
)