CODEWITHSUNDEEP
sql-servert-sqlpowerbidax
Serdia
Serdia

Reputation: 4428

Translate SELECT DISTINCT t-sql query to DAX expression

I need to create calculate table for the report in PowerBI Desktop. I know how to do that in t-sql but I am unable to interpret it to DAX. So should I use t-sql and add this query using "Get Data"? Or should I create calculate table using DAX? Which one is more efficient?

select distinct PolicyNumber,
        ReserveStatus, 
        case when ReserveStatus = 'Open' then 1 else 0 end as OpenStatus 
from RockhillClaimsDataFeed_PBI
group by PolicyNumber,ReserveStatus

Result looks like that:

enter image description here

can somebody help?

Upvotes: 2

Views: 1304

Answers (1)

Foxan Ng
Foxan Ng

Reputation: 7151

This is achievable by creating a calculated table in Power BI, with similar syntax using SELECTCOLUMNS and DISTINCT.

RockhillClaimsSummary = 
DISTINCT(
    SELECTCOLUMNS(
        RockhillClaims,
        "PolicyNumber", RockhillClaims[PolicyNumber],
        "ReserveStatus", RockhillClaims[ReserveStatus],
        "OpenStatus", IF(RockhillClaims[ReserveStatus] = "Open", 1, 0)
    )
)

Results:

results

Upvotes: 3

Related Questions