Forum Discussion

prashantg364's avatar
prashantg364
Helper II
3 years ago
Solved

Power query or DAX

Hi all,   I have a data set as shown in the input table, Against each product name & product band, I have 2 KPI categories, 1. 3 KPI and 2. 5 KPI. In columns 3 & 4 I have total pass KPIs in each c...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    Hi

    Add new columns

    =Text.Combine(List.Transform({{[KPI1],"KPI1"},{[KPI2],"KPI2"},{[KPI3],"KPI3"}}, each if _{0}=0 then _{1} else null), ",")

    and

    Text.Combine(List.Transform({{[KPI1],"KPI1"},{[KPI2],"KPI2"},{[KPI3],"KPI3"},{[KPI4],"KPI4"},{[KPI5],"KPI5"}}, each if _{0}=0 then _{1} else null), ",") 

    Stéphane 

Punithurs's avatar
Punithurs
Frequent Visitor
3 years ago

Hi prashantg364,
If you want to achieve this with the simple calculated column using dax. 

Step 1: 
Concatenate the columns.

Fail 3 = CONCATENATE(Sheet1[KPI1],CONCATENATE(Sheet1[KPI2],Sheet1[KPI3]))
And,
Text 3 = IF(Sheet1[Fail 3] = "111","",
IF(Sheet1[Fail 3] = "110","KPI3",
IF(Sheet1[Fail 3] = "100","KPI2,KPI3",
IF(Sheet1[Fail 3] = "001","KPI1,KPI2",
IF(Sheet1[Fail 3] = "010","KPI1,KPI3",
IF(Sheet1[Fail 3] = "011","KPI1",
"KPI1,KPI2,KPI3"))))))

 


 



We can use the same technique for other column as well