cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Help with creating new table using summarize

Hi Guys

 

I am able to solve this business problem but I am having trouble in applying filters to the same. 

 

So below is the data that I am working on: 

ID1DateDevice_IDNetwork_IDStateID2
101/06/201811Enabled101
101/06/201812Disabled102
101/06/201823Paused103
102/06/201824Enabled104
202/06/201835Disabled105
201/06/201811Paused106
202/06/201822Enabled107
301/06/201833Disabled108
301/06/201814Enabled109
301/06/201825Disabled110
301/06/201836Paused111
301/06/201811Enabled112
302/06/201821Disabled113
302/06/201832Paused114
302/06/201813Enabled115
402/06/201814Enabled116
501/06/201815Disabled117
501/06/201826Paused118
501/06/201821Enabled119
502/06/201832Disabled120
502/06/201833Paused121
502/06/201834Enabled122
502/06/201831Disabled123
503/06/201812Enabled124
503/06/201813Enabled125
602/06/201814Enabled126
601/06/201815Disabled127
602/06/201826Enabled128
603/06/201827Disabled129
603/06/201811Paused

130

 

This becomes little complicated now. So desired output is to count no of ID2 with enabled state for corresponding ID1. Result should look like this : 

ID1Count of ID2(enabled)
12
21
33
41
53
62

and I am able to get this result. Which tells me that no of ID2 which are enabled for ID1 ranges from 1 to 3 (1,2,3)

 

Now final output should look like below: 

No of ID2 enabledCount of ID1
12
22
32

This result is telling me that there are 2 ID1 which has 1 ID2 enabled. I am able to get this result but creating new tables using summarize. And I am not able to apply filters on my desired result because summarize didn't help. Is there any way where I can do this in the same table? or even if we need other table then I need to use filters.

 

Cheers

Shruti

5 REPLIES 5
Super User
Super User

Re: Help with creating new table using summarize

I believe that you probably need to look at using a measure here where you do the SUMMARIZE in a VAR to create a temp table. Tough to say because I'm not sure what kind of visualization you are going for. I'll see if I can look at this a little closer to decipher what you are going for or is it as simple as you want a table visualization of the last table you presented?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Re: Help with creating new table using summarize

@Greg_Deckler

 

I am just looking for a simple table which will show two columns and data will change when I apply some filters. 

Super User
Super User

Re: Help with creating new table using summarize

@Shruti_Goyal94

 

Try this technique

 

First create a single column calculated table

 

Parameters =
DATATABLE (
    "No of ID2 enabled", INTEGER,
    {
        { 1 },
        { 2 },
        { 3 },
        { 4 },
        { 5 }
    }
)

Now you can write a MEASURE (Assuming TableName is Table1)

 

Count of ID 1 =
VAR temp =
    SUMMARIZE (
        Table1,
        Table1[ID1],
        "Count of ID2", CALCULATE ( COUNT ( Table1[ID2] ), Table1[State] = "Enabled" )
    )
RETURN
    COUNTX (
        FILTER (
            temp,
            [Count of ID2] = SELECTEDVALUE ( Parameters[No of ID2 enabled] )
        ),
        [ID1]
    )

 

 

Super User
Super User

Re: Help with creating new table using summarize

@Shruti_Goyal94

 

See the attached file with your sample data

 

 

Re: Help with creating new table using summarize

@Zubair_Muhammad

 

You have created a table called parameters with values inserted in it manually. But the whole chanllenge with my problem i sto get those numbers populated automatically. I cannot add static column.