Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Safal
Frequent Visitor

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

Hi, 

 

i got 1 table, which has, 

Status, 

IDletters
01pp
02dd
03dd
04cc
05cc

 

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

 

Status2, 

NameSum
pp1
dd2
cc3

 

i have been using 

occurence = 
CountX(
    FILTER('Status', EARLIER('Status'[Status]) = 'Status'[Status]),
    'Status'[Status]
)
1 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] )
        )
)

vzhangti_0-1674800853271.png

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.

 

View solution in original post

4 REPLIES 4
Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

hi there, 

 

i got 1 table right now, which looks like this

 

IDDisciplineStatus
2528MechanicalClosed
2510MechanicalPending Review
2522ProcessClosed
2529ProcessPending
2530SystemPending Review
2531ArtPending Information

 

and i want to make another table, which gives me

the unqiue Discipline, and number of status

Unique Discipline IDUnique DisciplineNumber of status
101Mechanical2
102Process2
103System1
104Art1

 

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? 

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

vzhangti_0-1674800853271.png

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.

 

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!!!!!!!!!!!!!!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.