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
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler

 

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

 

 


Regards
Zubair

Please try my custom visuals

@Anonymous

 

See the attached file with your sample data

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

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.