Forum Discussion

breezym5's avatar
breezym5
Frequent Visitor
2 years ago

Fill blank values with 'Other'

Hi there! 

 

I have two tables in PBI model, one called Product and one called Customer. I have product_type and key1 in Product table, customer_id and key2 in Customer. I'm doing a many-to-one relationship between Customer and Product based on key1 and key2. Now, I'd like to count the number of customers for each product_type.

 

So, I've created a table like below where I used product_type from Product table and count distinct customer_ids from Customer table. But I noticed that one of the rows remains blank, and I'm assuming that this is due to unmatching data between Customer and Product. In other words, not all the key2 in Customer are in Product.

Product TypeNumber of Customers
 35
Sandles 65
Sneakers168

 

One solution I've tried was creating a new column in Product table, but it didn't work out as expected:

 

 

ProductType_New = 
IF(
    NOT(ISBLANK(MAXX('Customer', 'Customer'[key2]))),
    MAXX(FILTER('Customer', 'Customer'[key2] = 'Product'[key1]), 'Product'[product_type]),
    "Other"
)

 

 

Can someone please shed some light on how to fill blank values with 'Other'? Thanks in advance for any help!

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi, 

    One of many ways to solve this is to create a new row ("Others") in the product table and a new column ("New Product Key column that contains "Others") customer table in PQ editor, or even go to the further upstream (Datawarehouse or the source) to ask this.

    In general situation, this is a situation that we need to stop and ask data warehouse team (or to ask yourself) to check and provide the correct data without having any integrity issue.


    If that is not the case, please try something like below as a temporary solution if it suits your requirement.

     

     

     

    Customers count: =
SWITCH (
    TRUE (),
    HASONEVALUE ( 'Product New ct'[Product key] ),
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( 'Product New ct'[Product] ) = "Others",
                COUNTROWS (
                    FILTER (
                        Customer,
                        NOT ( Customer[Product key] IN ALL ( 'Product New ct'[Product key] ) )
                    )
                ),
            MAX ( Customer[Product key] ) = MAX ( Customer[Product key] ),
                COUNTROWS (
                    FILTER (
                        Customer,
                        Customer[Product key] IN VALUES ( 'Product New ct'[Product key] )
                    )
                )
        ),
    COUNTROWS ( Customer )
)

     

     

     

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi breezym5 ,

    you are right, blank is added in the visual when the items in the one-side column could not fully cover the items of the many-side column.
    it makes more sense to update your product table. 

     

    with a simplied dataset like:

    product   customer product
    A   Amy A
    B   Amber B
        Bob B
        Cindy C
        David D

    you may create a calcualted table like:

    table = 
VAR _table1 =
ADDCOLUMNS(
    DISTINCT(Products[Product]),
    "Count",
    CALCULATE(COUNTROWS(RELATEDTABLE(customer)))
) 
VAR _table2 = 
{(
    "Others", 
    DISTINCTCOUNT(customer[customer]) - SUMX(_table1, [Count])
)}
RETURN UNION(_table1, _table2)

     

    it worked like:

     

     

    it worked like: