Spliting columns values to separate columns

[Turka89]

Hi There,

I have a table of which is Recuritment table and it has column such as VacancyID, Vacancy Title and status.

VacancyID	VacnacyTitle	Status
1	V1	Filled
2	V2	Unfilled
3	V3	Cancelled

Status have drop down list of Filled, Unfilled and Cancelled. So when I drag status in PowerBi and filter the vacancies with filled only will show filled vacancies which is correct. 

VacancyID	VacancyTitle	Status
1	V1	Filled
2	V2	Filled
3	V3	Filled

What I would like to achieve is to have those status values if possible as columns and represented as 1 and 0 

VacancyID	Vacancy Title	Filled	Unfilled	Cancelled
1	V1	1	0	0
2	V2	0	1	0
3	V3	0	0	1

Thank you in advance.

[parry2k · Accepted solution]

@Turka89 add the following measure

Count = COUNTROWS ( YourTable ) + 0

Add matrix visual:

- on rows, add vacancy id and vacancy title columns

- on columns, add Status column

- on values, add Count measure (we created above)

Drill down to the next level in your matrix visual and format table, turn off step layout, it will show vacancy id and vacancy title on the same row. 

I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[parry2k · Accepted solution]

@Turka89 add the following measure

Count = COUNTROWS ( YourTable ) + 0

Add matrix visual:

- on rows, add vacancy id and vacancy title columns

- on columns, add Status column

- on values, add Count measure (we created above)

Drill down to the next level in your matrix visual and format table, turn off step layout, it will show vacancy id and vacancy title on the same row. 

I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Turka89]

@parry2k 

Thank you so much sir for the " + 0 "  helped me to achieve what I am looking for atleat. I had tho to use filter() to filter the values I need so its like COUNTROWS( FILTER ( table, table [columns] = "value")) + 0 did the trick.

I do tho need help again with multiple values in one column like COUNTROWS( FILTER ( table, table [columns] = "value" + "value" + "value")) it doesn't work

Any suggestions?

[parry2k · Accepted solution]

@Turka89 try this

COUNTROWS( FILTER ( table, table [columns] IN { "value1", "value2", "value3"} ))

I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Turka89]

@parry2k 

If  there wasn't a restriction on kudos I would've given you more thank you so much 

[parry2k]

@Turka89 appreciate your kind words. You are recognizing the effort that is very kind of you. Best!