CODEWITHSUNDEEP
sqlsql-serverdax
Neeru
Neeru

Reputation: 55

TSQL Query Statement in DAX for Power BI

I am trying to count all rows in a table in Power BI which uses a Where condition.

I'm not sure what I am doing wrong. Hoping you can help me out. Here is the TSQL Syntax and the results I want. When I execute the DAX query, I get nothing.

I need the DAX statement to work the same and generate the same results. I have been successful at using NOT IN in DAX, but for some reason, I am missing something for the IN clause.

TSQL:

SELECT Closed_Date
     , Closed_By
     , Count(*) CompletedTicket_By_Date
FROM Planning.dbo.IT_Support_Ticket
Where Status In ('Complete', 'Closed', 'Cancelled')
Group By Closed_Date
       , Closed_By

DAX:

Ticket Closed Measure = CALCULATE(COUNTROWS(Fact_IT_Support_Ticket)
,FILTER(Fact_IT_Support_Ticket, Fact_IT_Support_Ticket[Status]="Closed"),
Fact_IT_Support_Ticket[Status]="Complete", 
Fact_IT_Support_Ticket[Status]="Cancelled")

Upvotes: 0

Views: 280

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

This can be done using only one filter expression with multiple conditions using the OR operator ||.

Ticket Closed Measure =
CALCULATE (
    COUNTROWS ( Fact_IT_Support_Ticket ),
    FILTER (
        Fact_IT_Support_Ticket,
        Fact_IT_Support_Ticket[Status] = "Closed"
            || Fact_IT_Support_Ticket[Status] = "Complete"
            || Fact_IT_Support_Ticket[Status] = "Cancelled"
    )
)

As said in documentation the second argument of the filter function is a boolean expression that is evaluated per row. Note the expression above evaluates if every value in the Status column is Complete, Closed, Cancelled. The COUNTROWS function counts only the filtered rows in the given context.

REFERENCE

I've not tested the solution but it should work, let me know if this works for you.

Upvotes: 1

Related Questions