Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bsushy
Frequent Visitor

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:

Capture123.PNG

 

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? 

1 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" }
    }
)

t1.PNG

 

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. Smiley Happy

r1.PNG

Regards

View solution in original post

4 REPLIES 4
orourkebp
Frequent Visitor

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.

@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.

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" }
    }
)

t1.PNG

 

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. Smiley Happy

r1.PNG

Regards

Thank you:)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.