Create Bar Chart That Compares Counts Of Values Across Multiple Columns

[JDpowerBI]

Hi Everyone.  I have a challenging question around creating a specific type of bar chart.   Based on the data below, you can see that there are 5 Resources (column headers).  The Row data are  job IDs.  The values are names.  Any name can be used more then once per row, and with no order logic.    I have a few goals with this data.  The first goal is to produce a bar chart which counts how jobs each person participated in.  If a name is used twice per row, i want that counted as a distinct comination of name and job ID. 

The table 2nd table displays the count per name of distinct job/name combinations.  How does one accomdate this in a Power BI visual?  What keeps throwing me off too is that i'm not really incorporating the column header into the chart (Resource 1, Resource 2 etc).

Appreciate the help!

[Ashish_Mathur]

Hi,

RIght click on the Job ID number column in the Query Editor and select "Unpivot other columns".  Drag the Value field to the visual and write this measure

=DISTINCTCOUNT(Data[Job ID])

Hope this helps.

[JDpowerBI]

Thanks for the response @Ashish_Mathur !   That seems to have solved my problem.  However, by unpivoting the data, that seemed to have messed up the visualizations i had that were based on the data before being unpivoted.    I must also add that the table described above actually has many more columns in it (probably 30 or so) that i do NOT want included in this chart visual (But are relevant for other visualization on my dashboard).  In order to unpivot, it made sense to "Choose columns" and reduce the set of data to unpivot to only filter down to the columns that i want to unpivot).     

Does this mean i need multiple queries so some visualizations can be based on the unpivoted data, and others will be based on the normal set?

[Anonymous]

Hi @JDpowerBI,

Perhaps you can try to duplicate the original query table and do unpivot columns on it, save changes and return to the data view side. (you can use the category column as the relationship key to link both two tables)
After these steps, you can simply create visuals based on two table fields and do interaction between different visuals.
Regards,

Xiaoxin Sheng

[Ashish_Mathur]

You are welcome.  No, you should not need multiple queries.  All your visuals and calculations should be based on the unpivoted dataset.

[JDpowerBI]

Hi @Ashish_Mathur,. Since my "EVENT" table which contains the Resource columns listed above, and additional columns, how do i filter down the measure to only use specific columns in the count? =DISTINCTCOUNT(Data[Job ID])

I know we can use the filter where the visuals are too, but is that the only way?

[Ashish_Mathur]

Hi,

I do not understand your question.  Share some data and show the expected result.

[amitchandak]

I think you better unpivot this data https://radacad.com/pivot-and-unpivot-with-power-bi

Can you share sample data and sample output. Mark me @