Regulated Product - calculate column

[Anonymous]

Hello, 
I have an issue with calculate column in DAX. 
I want to show which asset in my report it's regulated asset. 
I have 2 seperate tables. In fisrt table "SOURCE" I have an information about all assets. 
In second table "REGULATED ASSET" I have only an information about regulated asset
I need to created calculate column in table "SOURCE" which will tell me which asset is regulated. 

I need to take into account too following stuffs:

Each row has to filled 3 columns:

• asset name
• asset status
• asset class (yellow columns)
  
  or this 3 columns:
• name
• fqdn
• IP (green columns)
  I can only count values if I have complete values at first in columns: asset name, asset status, asset class and if I don't have values in this 3 columns I take into account this 3 columns: name, fqdn, IP.
  
  and then I need to check which of the above assets are regulated and I want to create a calculated column that will give me these values like this one:
  
  

  but my calculete column doesn't display right vaules. Could you help me? 
  
  

  https://uewrc-my.sharepoint.com/:u:/g/personal/170361_student_ue_wroc_pl/EeYDwmeUn35NhYCX2We9DZYBfUp... 

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use the following calculated column formula if it suitable for your requirement:

REGULATED ASSET =
VAR list =
    CALCULATETABLE (
        VALUES ( 'REGULATED ASSET'[GROUP_NAME] ),
        FILTER (
            'REGULATED ASSET',
            'REGULATED ASSET'[NAME] = EARLIER ( Source[NAME] )
                || 'REGULATED ASSET'[NAME] = EARLIER ( Source[ASSET_NAME] )
        )
    )
RETURN
    IF ( COUNTROWS ( list ) > 0, CONCATENATEX ( list, [GROUP_NAME], "," ) )

Regards,

Xiaoxin Sheng