Forum Discussion

bsushy's avatar
bsushy
Frequent Visitor
8 years ago
Solved

Pivot data

 Below is my data:

MemberIDCancer ColumnHypertension ColumnDiabetes Column
1nndiabetes
2cancernn
2cancernn
3nndiabetes
4nhypertensiondiabetes
1nndiabetes
6cancerhypertensionn
2cancernn
5nhypertensionn
3nndiabetes

 

I would like to draw a graph as below:

 

cancer column, hypertension column, diabetes column correspond to chronic conditions.

y-axis should be the distinct count of members having that chronic condition.

How can i acheive this? do i need to create another table? 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi bsushy,

     

    As the Cancer Column, Diabetes and Hypertension Columns are calculated columns, they won't appear in Query Editor. So the Pivot option is not available here.

     

    Based on my test, you can firstly use the formula below to create a new table which has a column of Chronic Condition Types.

    Chronic Condition = 
DATATABLE (
    "Chronic Condition Type", STRING,
    {
        { "Cancer" },
        { "Diabetes" },
        { "Hypertension" }
    }
)

     

    Then you should be able to use the formula below to create a new measure to calculate distinct count of MemberID for each Chronic Condition Type from your original table('Table1').

    Measure = 
IF (
    HASONEVALUE ( 'Chronic Condition'[Chronic Condition Type] ),
    SWITCH (
        VALUES ( 'Chronic Condition'[Chronic Condition Type] ),
        "Cancer", CALCULATE (
            DISTINCTCOUNT ( Table1[MemberID] ),
            Table1[Cancer Column] = "cancer"
        ),
        "Hypertension", CALCULATE (
            DISTINCTCOUNT ( Table1[MemberID] ),
            Table1[Hypertension Column] = "hypertension"
        ),
        "Diabetes", CALCULATE (
            DISTINCTCOUNT ( Table1[MemberID] ),
            Table1[Diabetes Column] = "diabetes"
        )
    )
)

    And then you can show the 'Chronic Condition'[Chronic Condition Type] as Axis, and the measure as Value to get your expected result. :smileyhappy:

    Regards

4 Replies

  • orourkebp's avatar
    orourkebp
    Frequent Visitor
    8 years ago

    If I understand correctly, I suggest removing duplicate records during your data processing.  That way you will have a unique record for each member.  Then it should be a simple matter of graphing the table.  You may need to take the extra step of converting all of the "n" values to 0 and the non-"n" to 1.  Then you can simply sum the columns.

    • bsushy's avatar
      bsushy
      Frequent Visitor
      8 years ago

      orourkebp thank you for responding.

      How do i pivot calculated coulmns? Cancer Column, Diabetes and Hypertension Columns are calculated columns...I would like to pivot because it is easy to get those columns on a single axis.

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi bsushy,

         

        As the Cancer Column, Diabetes and Hypertension Columns are calculated columns, they won't appear in Query Editor. So the Pivot option is not available here.

         

        Based on my test, you can firstly use the formula below to create a new table which has a column of Chronic Condition Types.

        Chronic Condition = 
DATATABLE (
    "Chronic Condition Type", STRING,
    {
        { "Cancer" },
        { "Diabetes" },
        { "Hypertension" }
    }
)

         

        Then you should be able to use the formula below to create a new measure to calculate distinct count of MemberID for each Chronic Condition Type from your original table('Table1').

        Measure = 
IF (
    HASONEVALUE ( 'Chronic Condition'[Chronic Condition Type] ),
    SWITCH (
        VALUES ( 'Chronic Condition'[Chronic Condition Type] ),
        "Cancer", CALCULATE (
            DISTINCTCOUNT ( Table1[MemberID] ),
            Table1[Cancer Column] = "cancer"
        ),
        "Hypertension", CALCULATE (
            DISTINCTCOUNT ( Table1[MemberID] ),
            Table1[Hypertension Column] = "hypertension"
        ),
        "Diabetes", CALCULATE (
            DISTINCTCOUNT ( Table1[MemberID] ),
            Table1[Diabetes Column] = "diabetes"
        )
    )
)

        And then you can show the 'Chronic Condition'[Chronic Condition Type] as Axis, and the measure as Value to get your expected result. :smileyhappy:

        Regards