Group unmatched rows in one category

[yuanye0710]

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?

[Anonymous]

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. 

[Rupak_bi]

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

[yuanye0710]

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.

[Rupak_bi]

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.

[Sahir_Maharaj · Accepted solution]

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!

[yuanye0710]

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.