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

Visualize the following data efficiently

I have the following data:

 

Name of the projectReason 1Reason 2Reason 3Reason 4Reason 5Reason 6Reason 7
AminoYes No YesYesNoYesNo
AminoNoYesNoYesYesNoYes
BashYesNoYesNoNoYesNo
TrashYesNoNoYesYesNoYes

 

How do I effectively and efficiently visualise the above table especially when I want to Identify the number of Yes and Nos for each reason per project?

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @Novice_ ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

 

Best Regards

Lucien

v-luwang-msft
Community Support
Community Support

Hi @Novice_ ,

Final showing like below:

v-luwang-msft_0-1621579870212.png

 

Use the following dax to create new column:

Count_Yes = 
VAR test1 =
    IF ( 'Table'[Reason 1] = "Yes", 1, 0 )
VAR test2 =
    IF ( 'Table'[Reason 2] = "Yes", 1, 0 )
VAR test3 =
    IF ( 'Table'[Reason 3] = "Yes", 1, 0 )
VAR test4 =
    IF ( 'Table'[Reason 4] = "Yes", 1, 0 )
VAR test5 =
    IF ( 'Table'[Reason 5] = "Yes", 1, 0 )
VAR test6 =
    IF ( 'Table'[Reason 6] = "Yes", 1, 0 )
VAR test7 =
    IF ( 'Table'[Reason 7] = "Yes", 1, 0 )
RETURN
    test1 + test2 + test3 + test4 + test5 + + test6 + test7
Count_No = 
VAR test1 =
    IF ( 'Table'[Reason 1] = "No", 1, 0 )
VAR test2 =
    IF ( 'Table'[Reason 2] = "No", 1, 0 )
VAR test3 =
    IF ( 'Table'[Reason 3] = "No", 1, 0 )
VAR test4 =
    IF ( 'Table'[Reason 4] = "No", 1, 0 )
VAR test5 =
    IF ( 'Table'[Reason 5] = "No", 1, 0 )
VAR test6 =
    IF ( 'Table'[Reason 6] = "No", 1, 0 )
VAR test7 =
    IF ( 'Table'[Reason 7] = "No", 1, 0 )
RETURN
    test1 + test2 + test3 + test4 + test5 + + test6 + test7
Count_No = 
VAR test1 =
    IF ( 'Table'[Reason 1] = "No", 1, 0 )
VAR test2 =
    IF ( 'Table'[Reason 2] = "No", 1, 0 )
VAR test3 =
    IF ( 'Table'[Reason 3] = "No", 1, 0 )
VAR test4 =
    IF ( 'Table'[Reason 4] = "No", 1, 0 )
VAR test5 =
    IF ( 'Table'[Reason 5] = "No", 1, 0 )
VAR test6 =
    IF ( 'Table'[Reason 6] = "No", 1, 0 )
VAR test7 =
    IF ( 'Table'[Reason 7] = "No", 1, 0 )
RETURN
    test1 + test2 + test3 + test4 + test5 + + test6 + test7
Reason_No = 
VAR test1 =
    IF ( 'Table'[Reason 1] = "No", "Reason 1", BLANK() )
VAR test2 =
    IF ( 'Table'[Reason 2] = "No", "Reason 2", BLANK() )
VAR test3 =
    IF ( 'Table'[Reason 3] = "No", "Reason 3", BLANK() )
VAR test4 =
    IF ( 'Table'[Reason 4] = "No","Reason 4", BLANK() )
VAR test5 =
    IF ( 'Table'[Reason 5] = "No", "Reason 5", BLANK() )
VAR test6 =
    IF ( 'Table'[Reason 6] = "No", "Reason 6", BLANK())
VAR test7 =
    IF ( 'Table'[Reason 7] = "No", "Reason 7", BLANK())
RETURN
    test1&" "&test2&" "&test3&" "&test4&" "&test5&" "&test6&" "&test7

 

 

You could download my pbix flie if you need.

 

Wish it is helpful for you!

 

Best Regards

Lucien

amitchandak
Super User IV
Super User IV

@Novice_ , My advice would be unpivoting all the reason columns

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

 

Then reason would also be a column. ANd you can use matrix for similar display with max of value



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors