Forum Discussion

SINGHTX9's avatar
SINGHTX9
Icon for Helper II rankHelper II
7 years ago

How to transpose columns in a calculated table

Hi all    I m trying to create a pie chart with 3 columns (CC alone, IA alone and IA and CC )and it is not displaying the data correctly. Could it be because my data is spread across multiple colum...
Need Help
Chihiro's avatar
Chihiro
Icon for Solution Sage rankSolution Sage
7 years ago

Took a look. You can build a relationship based on Accession, but you won't be able to filter on Priority.

 

Since some Accession has both STAT & Routine assigned to it, it creates ambiguity.

 

I'd suggest starting from top and rethink how your model should be structured to summarize your information.

SINGHTX9's avatar
SINGHTX9
Icon for Helper II rankHelper II
7 years ago

Actually this particular data set has some accession id having both STAT and routine but ideally this should not be the case.

 

Given that a particular accession id is either STAT or routine can building the relationship based on accession help me to filter on priority?

 

Did you happen to take a look at the capacity charts as well?

  • Chihiro's avatar
    Chihiro
    Icon for Solution Sage rankSolution Sage
    7 years ago

    I'd recommend doing data clean up before loading to model.

     

    But you can simulate how relationship will work using following method.

     

    1. Add another table that summarize by unique Accession and retrieve FIRSTNONBLANK for Priority associated.

    Table =
SUMMARIZE (
    'Actual data',
    'Actual data'[Accession  ],
    "Priority", FIRSTNONBLANK (
        'Actual data'[Priority ],
        GROUPBY ( 'Actual data', 'Actual data'[Accession  ] )
    )
)

    2. Then modify your Specimen distribution table to include Accession.

    Specimen distribution =
UNION (
    SELECTCOLUMNS (
        'Final Table',
        "Accession", [Accession  ],
        "Specimen Distribution", "CC",
        "Value", 'Final Table'[CC alone]
    ),
    SELECTCOLUMNS (
        'Final Table',
        "Accession", [Accession  ],
        "Specimen Distribution", "IA",
        "Value", 'Final Table'[IA alone]
    ),
    SELECTCOLUMNS (
        'Final Table',
        "Accession", [Accession  ],
        "Specimen Distribution", "IA and CC",
        "Value", 'Final Table'[IA AND CC]
    )
)

     

    Use Accession from created table to relate the two. You can now use Priority to filter visual(s) based on related data.