Forum Discussion

srobs's avatar
srobs
Frequent Visitor
2 years ago
Solved

Link Columns as one data source

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
srobs
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.