Create a measure with LOOKUPVALUE

[liselotte]

I have the following tables:
- dimension table dim_customer with columns: Customer UID, Customer Name
- dimension table dim_country with columns Country, Customer Name, Account UID (each Customer Name has only 1 Country and 1 Account UID)
- dimension date table dim_date with column Date
- fact table fact_amount with columns Customer UID, Account UID, Date, Amount

Tables dim_customer, dim_country, and dim_date connect to table fact_amount via columns Customer UID, Account UID, and Date, respectively. 

I want to create a visual table with columns Customer Name, Country, Date, Sum_Amount which is sum of Amount of corresponding Customer UID instead of Account UID. I use this DAX query:

Sum_Amount =
var _uid = LOOKUPVALUE('dim_customer '[Customer UID], 'dim_customer '[Customer Name], MAX('dim_Country'[Customer Name]))
return
CALCULATE(SUM('fact_amount'[Amount])
    ,REMOVEFILTERS('dim_country')
    , FILTER('fact_amount', [Customer UID]=_uid)
)

But it shows blank values for all of Customer/Country. Could anyone help me with this, please? I know I could change the relationship among the table to make it easier, but my data model is more complicated and requires the relationships to be set up as described for other visuals. Thank you.

[Anonymous]

Hi @liselotte ,

I created a sample pbix file(see the attachment), please check if that is what you want.

If you don't want to create the relationships among these tables, you can create a measure as below to get it:

Measure = 
VAR _custname =
    SELECTEDVALUE ( 'dim_customer'[Customer Name] )
VAR _custid =
    SELECTEDVALUE ( 'dim_customer'[Customer UID] )
VAR _coutryid =
    SELECTEDVALUE ( 'dim_country'[Country] )
VAR _date =
    SELECTEDVALUE ( 'dim_date'[Date] )
VAR _accids =
    CALCULATETABLE (
        VALUES ( 'dim_country'[Account UID] ),
        FILTER (
            'dim_country',
            'dim_country'[Country] = _coutryid
                && 'dim_country'[Customer Name] = _custname
        )
    )
RETURN
    CALCULATE (
        SUM ( 'fact_amount'[Amount] ),
        FILTER (
            'fact_amount',
            'fact_amount'[Customer UID] = _custid
                && 'fact_amount'[Date] = _date
                && 'fact_amount'[Account UID] IN _accids
        )
    )

If the above ones can't help you figure out, could you please provide more details(sample data, screenshot etc.) and expected result base on my sample pbix file? Thank you.

Best Regards

[liselotte]

Hi Rena @Anonymous ,

Thank you very much for your quick response.

The problem of my data is that column Account UID in table dim_country may contain missing data as NULLs. That's why I have a relationship based on Account UID to fact table but I want to calculate based on Customer UID instead.

I modified your file to make it similar to my real data with changing an Account UID to NULL in table dim_country, making Country to be unique, and establishing the relationship as follows:

After that, the resulted visual table doesn't show a row for Cust02 which is expected to show 5172.

Your resulted table is exactly what I wanna achieve. Could you please help me with it again? Thank you very much.

[Anonymous]

Hi @liselotte ,

I updated the sample pbix file(see the attachment), please check if that is what you want. You can update the formula of measure as below to get it:

Measure = 
VAR _custname =
    SELECTEDVALUE ( 'dim_customer'[Customer Name] )
VAR _custid =
    SELECTEDVALUE ( 'dim_customer'[Customer UID] )
VAR _coutryid =
    SELECTEDVALUE ( 'dim_country'[Country] )
VAR _date =
    SELECTEDVALUE ( 'dim_date'[Date] )
VAR _country1 = 
    CALCULATE(MAX('dim_country'[Country]),FILTER('dim_country','dim_country'[Customer Name]=_custname&&'dim_country'[Country]=_coutryid))
RETURN if(_country1=_coutryid,
    CALCULATE (
        SUM ( 'fact_amount'[Amount] ),
        FILTER (
            'fact_amount',
            'fact_amount'[Customer UID] = _custid
                && 'fact_amount'[Date] = _date 
        ) 
    ))

Best Regards

[liselotte]

Hi Rena @Anonymous ,

Thank you for your kind support. When I set up relationships as I mentioned in your provided file. The table doesn't show the Country for Country2

Could you please make it show Country02?

[Anonymous]

Hi @liselotte ,

Please find the details in the attachment:

Best Regards

[liselotte]

@Anonymous  In your screenshot, it still doesn't show "Country2" in column "Country". Could you please check it again?

[Anonymous · Accepted solution]

Hi @liselotte ,

If you create the relationship between the table 'dim_country' and 'fact_amount' base on the field [Account UID], it will return the NULL value for Cust02. Please remove the relationship just as shown in below screenshot.

Best Regards

[liselotte]

Thank you very much for your help and clarification. 🙂