topic Re: Group unmatched rows in one category in DAX Commands and Tips

Group unmatched rows in one category

yuanye0710 — Wed, 20 Nov 2024 12:52:58 GMT

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:

Code	Description
M	Male
F	Female
U_	Unknown

And the bigger table is:

Student	Gender
1	M
2	M
3	F
4	AAA
5	BBB
6	CCC

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?

Re: Group unmatched rows in one category

Sahir_Maharaj — Wed, 20 Nov 2024 13:28:58 GMT

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!

Re: Group unmatched rows in one category

Rupak_bi — Wed, 20 Nov 2024 13:37:36 GMT

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

Re: Group unmatched rows in one category

yuanye0710 — Wed, 20 Nov 2024 15:34:39 GMT

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.

Re: Group unmatched rows in one category

yuanye0710 — Wed, 20 Nov 2024 15:35:37 GMT

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.

Re: Group unmatched rows in one category

Anonymous — Thu, 21 Nov 2024 03:04:13 GMT

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

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

(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"))

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.

Re: Group unmatched rows in one category

Rupak_bi — Thu, 21 Nov 2024 06:05:04 GMT

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.