need help; making a new table with distinct values from another table.

[Safal]

Hi, 

i got 1 table, which has, 

Status, 

ID	letters
01	pp
02	dd
03	dd
04	cc
05	cc

i want to make another table, from Colum table, where it shows, 

Status2, 

Name	Sum
pp	1
dd	2
cc	3

i have been using 

occurence = 
CountX(
    FILTER('Status', EARLIER('Status'[Status]) = 'Status'[Status]),
    'Status'[Status]
)

[Greg_Deckler]

@Safal Not sure how your formula matches up with the data you provided. If you put letters into a table visual then you should just be able to use something like COUNTROWS(DISTINCT(RELATEDTABLE('Status'))). But, seems like some information is missing so hard to be certain. 

[Safal]

hi there, 

i got 1 table right now, which looks like this

ID	Discipline	Status
2528	Mechanical	Closed
2510	Mechanical	Pending Review
2522	Process	Closed
2529	Process	Pending
2530	System	Pending Review
2531	Art	Pending Information

and i want to make another table, which gives me

the unqiue Discipline, and number of status

Unique Discipline ID	Unique Discipline	Number of status
101	Mechanical	2
102	Process	2
103	System	1
104	Art	1

essentially, 

i am trying to make a drill down report in which, 

i got a bar graph which shows me the Discipline and number of status, 

when i click on the bar, it will change into type of status for each discipline stacked bar chart or change another chart.  I am able to do this on Javascript, however, im struggling with creating tables on powerBI using the raw data. 

can you give me directions please? 

[v-zhangti · Accepted solution]

Hi, @Safal 

You can try the following methods.

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[Discipline],
    "Number of status",
        CALCULATE (
            COUNT ( 'Table'[Status] ),
            ALLEXCEPT ( 'Table', 'Table'[Discipline] )
        )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Safal]

Hi there, thank you so much for your reply. 

Ive changed the tables names and used it in my work. 

Appreaciate your help on this. PowerBI is amazing but quite hard to use. I love the community for it!!!!!!!!!!!!!!!