Forum Discussion
Create a measure with LOOKUPVALUE
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
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
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.
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