Forum Discussion

liselotte's avatar
liselotte
Advocate I
1 year ago
Solved

Create a measure with LOOKUPVALUE

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 N...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    liselotte
    Advocate I
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        liselotte
        Advocate I
        1 year ago

        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?