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 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
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?