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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
yuanye0710
Frequent Visitor

Group unmatched rows in one category

Hi friends, for my case I have a lookup table connect to another bigger table. The problem is there are unmatched rows and whenever I want to plot something I'd like to put all unmatched rows into one category.

 

For example, I have a Gender lookup table like this:

CodeDescription
MMale
FFemale
U_Unknown

 

And the bigger table is:

StudentGender
1M
2M
3F
4AAA
5BBB
6CCC

 

My relationship is Code column connects to the Gender column. Note that there are 3 rows (in red) not found in the lookup table.

 

Say I want to plot a bar chart by counting the Gender. For Student 4, 5 and 6 I want to put them into the "Unknown" Category. How can I do that?

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @yuanye0710,

 

Can you please try using a measure to handle the grouping dynamically:

Gender Count = 
CALCULATE(
    COUNTROWS('Bigger Table'),
    'Gender Lookup'[Code] IN VALUES('Bigger Table'[Gender])
) + 
CALCULATE(
    COUNTROWS('Bigger Table'),
    ISBLANK(
        LOOKUPVALUE('Gender Lookup'[Description], 'Gender Lookup'[Code], 'Bigger Table'[Gender])
    )
)

Hope this helps!


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

6 REPLIES 6
v-tangjie-msft
Community Support
Community Support

Hi @yuanye0710 ,

 

First of all, thanks to Rupak_bi  and Sahir_Maharaj  for the quick reply and solution. I have some other thoughts to add:
Due to the design, taking the Stacked column chart as an example, the measure does not support being placed on the x-axis and legend. So if you need to create columns.
(1) Create a calculated column.

Column = IF([Gender] in VALUES('Gender lookup table'[Code]),[Gender],"Unknown")

vtangjiemsft_0-1732157519033.png

(2) Or add a column in Power query.

Merge queries as new->expand table->add new custom column->delete [Gender lookup table.Code] column and [gender] column ->Close and apply.

vtangjiemsft_1-1732157580729.png

vtangjiemsft_2-1732157614871.png

vtangjiemsft_3-1732157766634.png

(3) Create a calculation table.  You can use SELECTCOLUMNS() function to create a new table by selecting only the required columns.

Table = ADDCOLUMNS(SELECTCOLUMNS('bigger table',[Gender],[Student]),"group",IF([Gender] in VALUES('Gender lookup table'[Code]),[Gender],"Unknown"))

vtangjiemsft_4-1732157991598.png

Best Regards,

Neeko Tang

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

Rupak_bi
Post Prodigy
Post Prodigy

Hi @yuanye0710 ,

 

The simple way is to create a calculated column in the bigger table like below and create relationship to this column.
New Gender = switch(table,Gender,"M","M","F","F",U_)



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/

Thank you very much for your reply. I thought about this before but in reality the bigger table is over 20M rows and 30+ columns, I also have 10+ lookup tables, so I really want to avoid adding additional new columns. I'll keep this mind though in case I don't have other options.

Hi @yuanye0710 ,

 

If you wish to use a measure to count, here is the DAX to use. Remember there should not be any relationship between two tables.

Rupak_bi_0-1732169044786.png

 





Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/
Sahir_Maharaj
Super User
Super User

Hello @yuanye0710,

 

Can you please try using a measure to handle the grouping dynamically:

Gender Count = 
CALCULATE(
    COUNTROWS('Bigger Table'),
    'Gender Lookup'[Code] IN VALUES('Bigger Table'[Gender])
) + 
CALCULATE(
    COUNTROWS('Bigger Table'),
    ISBLANK(
        LOOKUPVALUE('Gender Lookup'[Description], 'Gender Lookup'[Code], 'Bigger Table'[Gender])
    )
)

Hope this helps!


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Thanks a lot I will give it a try. What I didn't mention is my table is 20M+ rows and 30+ columns, I also have 10+ lookup tables like Gender, so efficiency is a big concern. I'll test it out and see how it goes.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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