Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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?
Solved! Go to 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!
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.
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_)
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.
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!
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.
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
11 |