Help with creating new table using summarize

[Anonymous]

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: 

ID1	Date	Device_ID	Network_ID	State	ID2
1	01/06/2018	1	1	Enabled	101
1	01/06/2018	1	2	Disabled	102
1	01/06/2018	2	3	Paused	103
1	02/06/2018	2	4	Enabled	104
2	02/06/2018	3	5	Disabled	105
2	01/06/2018	1	1	Paused	106
2	02/06/2018	2	2	Enabled	107
3	01/06/2018	3	3	Disabled	108
3	01/06/2018	1	4	Enabled	109
3	01/06/2018	2	5	Disabled	110
3	01/06/2018	3	6	Paused	111
3	01/06/2018	1	1	Enabled	112
3	02/06/2018	2	1	Disabled	113
3	02/06/2018	3	2	Paused	114
3	02/06/2018	1	3	Enabled	115
4	02/06/2018	1	4	Enabled	116
5	01/06/2018	1	5	Disabled	117
5	01/06/2018	2	6	Paused	118
5	01/06/2018	2	1	Enabled	119
5	02/06/2018	3	2	Disabled	120
5	02/06/2018	3	3	Paused	121
5	02/06/2018	3	4	Enabled	122
5	02/06/2018	3	1	Disabled	123
5	03/06/2018	1	2	Enabled	124
5	03/06/2018	1	3	Enabled	125
6	02/06/2018	1	4	Enabled	126
6	01/06/2018	1	5	Disabled	127
6	02/06/2018	2	6	Enabled	128
6	03/06/2018	2	7	Disabled	129
6	03/06/2018	1	1	Paused	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 : 

ID1	Count of ID2(enabled)
1	2
2	1
3	3
4	1
5	3
6	2

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 enabled	Count of ID1
1	2
2	2
3	2

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

[Greg_Deckler]

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?

[Anonymous]

@Greg_Deckler

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

[Zubair_Muhammad]

@Anonymous

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]
    )

[Zubair_Muhammad]

@Anonymous

See the attached file with your sample data

[Anonymous]

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