Forum Discussion

yuanye0710's avatar
yuanye0710
Frequent Visitor
1 year ago
Solved

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?

DAX
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    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!

6 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      yuanye0710
      Frequent Visitor
      1 year ago

      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.

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      yuanye0710
      Frequent Visitor
      1 year ago

      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's avatar
        Rupak_bi
        Icon for Super User rankSuper User
        1 year ago

        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.

         



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.