Forum Discussion
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:
| 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
5 Replies
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?
I am just looking for a simple table which will show two columns and data will change when I apply some filters.
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] )
