Pivot data

[bsushy]

 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
1	n	n	diabetes
6	cancer	hypertension	n
2	cancer	n	n
5	n	hypertension	n
3	n	n	diabetes

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? 

[orourkebp]

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]

@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 · Accepted solution]

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. 

Regards

[bsushy]

Thank you:)