Forum Discussion

Mace's avatar
Mace
New Member
4 years ago
Solved

Represent multiple column values in separate columns instead of separate rows

Hello, I'm new to PBI and this thing may be trivial. When I make a table of data i have the situation as on the first picture (multiple rows for different values in the column) and would wan...
General Comment
Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Mace 

    Dax code, create 3 columns,

    test1 = IF(CALCULATE(COUNT('Table'[CHANNEL]),FILTER(ALLEXCEPT('Table','Table'[MFG_P],'Table'[PART_NUMBER]),'Table'[CHANNEL]="DIST"))>0,"DIST")
    test2 = IF(CALCULATE(COUNT('Table'[CHANNEL]),FILTER(ALLEXCEPT('Table','Table'[MFG_P],'Table'[PART_NUMBER]),'Table'[CHANNEL]="EMS"))>0,"EMS")
    test3 = IF(CALCULATE(COUNT('Table'[CHANNEL]),FILTER(ALLEXCEPT('Table','Table'[MFG_P],'Table'[PART_NUMBER]),'Table'[CHANNEL]="OEM"))>0,"OEM")

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

Hi Mace ,

 

You can create a column with below code:-

channel_dist = if(table[channel]="DIST",table[channel])
channel_ems = if(table[channel]="EMS",table[channel])
channel_oem = if(table[channel]="OEM",table[channel])

 

Thanks,

Samarth

Mace's avatar
Mace
New Member
4 years ago

Samarth_18 , but would i loos the unnecessary rows?

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Mace , In that case you can create a column in Edit query and remove unneccesary column after there.

    • Mace's avatar
      Mace
      New Member
      4 years ago

      Samarth_18I want to create this table from company dataset and create live charts. I don't want to edit the table every time.

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        Mace  its a one time step only. 🙂