cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aammk Frequent Visitor
Frequent Visitor

Displaying Yes, No, NA values

Hello,

Fairly new to Power Bi and I am trying to put into a chart the number of projects that have submitted or missing documents.

 

There are about 7 different "documents" in seperate columns that have a primary key being the "Project's ID". Each Project will have either a "yes", "no", or "NA" value for each document column. I now I want to display in power BI desktop the number of projects and the corresponding document that they have said "yes" to and the projects that said "no" and what the documents were.

 

I've tried assigning the text into 1,2,3 data values and seeing if I can display it like that but it seems I am only getting a count and don't know how to isolate them into seperate views - the yes, the no, the NA.

 

Thank you for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft yingyinr
Microsoft

Re: Displaying Yes, No, NA values

Hi @aammk,

I just create the sample file based on your case description, assume that you have similar dataset with the table testCountPro in below screenshot.     table.JPG

First, translate these documents columns into attribute-value pairs

  • Click Edit queries > Edit queries, navigate to Power Query Editor
  • Copy the table testCountPro and paste with name “testCountPro2”, select this new table (make backup for the original table)
  • Select all documents columns(Doc1~Doc7), then click “Unpivot Columns”      unpivot.JPG

Then create one calculated colum calSta

calSta = SWITCH('testCountPro2'[Value],"Yes",1,"No",2,"NA",3)

 

Finally, create one matrix visual, put the related fields onto matrix as below:       calculate project number.jpg

If the above method is not applicable in your scenario, please provide the related table structure and sample data.

 

Best Regards

Rena

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Displaying Yes, No, NA values

Hi @aammk ,

 

could you provide some sample data?

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Proud to be a Super User!

Microsoft yingyinr
Microsoft

Re: Displaying Yes, No, NA values

Hi @aammk,

I just create the sample file based on your case description, assume that you have similar dataset with the table testCountPro in below screenshot.     table.JPG

First, translate these documents columns into attribute-value pairs

  • Click Edit queries > Edit queries, navigate to Power Query Editor
  • Copy the table testCountPro and paste with name “testCountPro2”, select this new table (make backup for the original table)
  • Select all documents columns(Doc1~Doc7), then click “Unpivot Columns”      unpivot.JPG

Then create one calculated colum calSta

calSta = SWITCH('testCountPro2'[Value],"Yes",1,"No",2,"NA",3)

 

Finally, create one matrix visual, put the related fields onto matrix as below:       calculate project number.jpg

If the above method is not applicable in your scenario, please provide the related table structure and sample data.

 

Best Regards

Rena

View solution in original post

aammk Frequent Visitor
Frequent Visitor

Re: Displaying Yes, No, NA values

Hi Rena,

 

The dataset you created is exactly the type I am using and your steps were easy to follow, much appreciated!

 

Thank you,

Andrew

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors