CODEWITHSUNDEEP
daxssas-tabular
ramadongre
ramadongre

Reputation: 101

Calculate distinct of a table column plus two static values using DAX

I have a tabular cube that I working on. I would like to get distinct values from "Column1" and then get two additional columns(to be created afresh) as static text one as "All" and other as "2020Q4".

Output would look like:

Column1 Column2 Column3
AA All 2020Q4
BB All 2020Q4
CC All 2020Q4
DD All 2020Q4
EE All 2020Q4

Any help is appreciated. So far I am able to get distinct but unable to get associated static values.

Upvotes: 1

Views: 476

Answers (2)

RADO
RADO

Reputation: 8148

Your can simplify the code:

EVALUATE
ADDCOLUMNS (
    DISTINCT ( TableName[Column1] ),
    "Column2", "All",
    "Column3", "2020Q4"
)

Upvotes: 2

ramadongre
ramadongre

Reputation: 101

Finally able to do it as below:

 EVALUATE 
       Addcolumns( DISTINCT(Tblname[Column1]), "Column2", 
 SELECTCOLUMNS(Tblname, "Column2", SUBSTITUTE(Tblname[Column1], 
 Tblname[Column1], "All")),
 "Column3", SELECTCOLUMNS(Tblname, "Column3", SUBSTITUTE(Tblname[Column1], 
 Tblname[Column1], "2020Q4"))
 )

Upvotes: 0

Related Questions