Link Columns as one data source

[srobs]

Hi all, 

I'm importing defect data from a JIRA query through a .csv.

Some of the JIRA fields have multiple responses, e.g. "Instrument Affected" could have Instrument a, instrument b, instrument c etc, it could just have 1 or some have upto 12 called out.

These all then become separate columns. I want to pull out data for Instruments affected, like "how many defects impact instrument a" but I have 12 columns of instrument data and can't find a way to add all columns to a visual I can use, or how to merge them in a way that the data points remain separate. 

Can anyone help? 

[srobs]

Hi, thanks for your responses. 

I can use the unpivot to get a count for each instrument which is nice, but what I want to be able to do is drill down into my information by instrument without having to have a different slicer for each column. 

I have other columns with more data like issue acceptability, status, priotrity etc and I'd like to be able to view this data per instrument. 

Does that make sense? 

Thanks

[Anonymous]

Hi @srobs ,

I can implement your request for you, but I would highly discourage you from doing so, as it would be very much more complicated than the previous method of unpivot.
I can give you an example:
Here is the sample data:

And I add a new table for creating the slicer:

There is no relationship between two tables:

Then use this DAX to create a new measure:

Count with Instrument = 
VAR SelectedInstrument =
SELECTEDVALUE(Slicer[Instrument])
RETURN
IF(
    ISFILTERED(Slicer[Instrument]),
    (
        IF(
            CONTAINS('Table (2)', 'Table (2)'[Custom Field(Instrument Impacted)], SelectedInstrument),
            1,
            0
        ) +
        IF(
            CONTAINS('Table (2)', 'Table (2)'[Custom Field(Instrument Impacted).1], SelectedInstrument),
            1,
            0
        ) +
        IF(
            CONTAINS('Table (2)', 'Table (2)'[Custom Field(Instrument Impacted).2], SelectedInstrument),
            1,
            0
        ) +
        IF(
            CONTAINS('Table (2)', 'Table (2)'[Custom Field(Instrument Impacted).3], SelectedInstrument),
            1,
            0
        ) +
        IF(
            CONTAINS('Table (2)', 'Table (2)'[Custom Field(Instrument Impacted).4], SelectedInstrument),
            1,
            0
        )
    ),
    BLANK()
)

The final output is like below:

But the problem with this method is that it can only calculate one field at a time. For example, in the example we are calculating Custom Field, if you have other fields like issue acceptability, status, priotrity etc, then you need to create as many measures as you have fields.
Additionally, for each field of the measure, which contains as many columns of data as you need to write code for in the measure. 
Personally, I don't think this is a better approach for the previous unpivot, and I don't think there is a way to implement a way to calculate the counts for all fields with just one measure.

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

[Anonymous · Accepted solution]

Hi @srobs ,

Please try this way:
Select all of the columns and select "Unpivot Columns":

Then select "Value" and select "Group By". Make the settings as shown in the following figure:

The final output is below:

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

[srobs]

Hi, 

I've found a way to show what I wanted now!

I used this to unpivot and then removed the blanks and I can display the data by instrument type.

Thanks for your help. 

[Daniel29195]

@srobs 

maybe unpivoting your table would make things easier for you ? 

unpivot your table  from power query : 

step1 :  go to power query

step2 :  select your table

step3 :  go to transform 

step4 :  select the columns from the table you want to unpviot

step5  : 

now you can just use extract between delimiters to and set : ( ) . 

NB : you columns names should all have value that you want to appear in the column inside two (   ) 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠