Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Consolidated Company Revenue Using DAX

Hi,   First of all, let me explain what type of data structure I'm dealing with. I work for a cross border logistics company. Therefore, the data model is constructed from two sources (2 companies)...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Anonymous ,

     

    I would create two new calculated columns shipper (JDWA) and consignee (JDWA).

    The DAX for the calculated column shipper (JDWA) would be similar to this:

    shipper (JDWA) = 
if('tablename'[Company Code] = "JDWA"
,'tablename'[Shipper name]
,var __MasterJobNo = 'tablename'[Master Job No]
var shipperJDWA = LOOKUPVALUE(
	'tablename'[Shipper Name] ,
	'tablename'[Company Code] , "JDWA"
	'tablename'[Master Job No] , __MasterJobNo
)
return
if(ISBLANK(shipperJDWA), 'tablename'[Shipper Name] , __MasterJobNo)

    Then you can use both new columns as slicer, using the value from the JDWA row.

     

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey Anonymous ,

 

I would create two new calculated columns shipper (JDWA) and consignee (JDWA).

The DAX for the calculated column shipper (JDWA) would be similar to this:

shipper (JDWA) = 
if('tablename'[Company Code] = "JDWA"
,'tablename'[Shipper name]
,var __MasterJobNo = 'tablename'[Master Job No]
var shipperJDWA = LOOKUPVALUE(
	'tablename'[Shipper Name] ,
	'tablename'[Company Code] , "JDWA"
	'tablename'[Master Job No] , __MasterJobNo
)
return
if(ISBLANK(shipperJDWA), 'tablename'[Shipper Name] , __MasterJobNo)

Then you can use both new columns as slicer, using the value from the JDWA row.

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dear TomMartens 

 

Thank you for your support. Your suggestion got me to the right answer.

 

Thank you and best regards,

Paniti