Forum Discussion

bsushy's avatar
bsushy
Frequent Visitor
8 years ago
Solved

Pivot data

 Below is my data: MemberID Cancer Column Hypertension Column Diabetes Column 1 n n diabetes 2 cancer n n 2 cancer n n 3 n n diabetes 4 n hypertension diabetes ...
  • 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

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

      • bsushy's avatar
        bsushy
        Frequent Visitor
        8 years ago

        Thank you:)