How to combine two columns in a visual that is a table that is joning two other tables in data model

[Anonymous]

How do I create a visual that is a table that combines two columns into one column? I have a visual that is using data from two separate tables. Here is the join in the data model:

I was able to create a visual that shows the two columns, but I want to combine the two columns into one. It is a date field that exists in either one of the two tables (but not both). I want just one combined column. (In the below picture I want to combine "Loading Date" with "STO Load Date"

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

TableA:

TableB:

There is a one-to-one relationship between tables.

You may create a measure like below.

Result = 
var _loadingdate = SELECTEDVALUE(TableA[Loading Date])
var _stoloadingdate = SELECTEDVALUE(TableB[STO Load Date])

return 
IF(
    ISBLANK(_loadingdate),
    _stoloadingdate,
    IF(
        ISBLANK(_stoloadingdate),
        _loadingdate,
        _stoloadingdate
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks! I think that is one step away. My data actually does have some rows after my join that are blank for both values. If both values are blank then it should be blank.

So, the logic should be this:

If there is an STO Load Date then use STO Load Date, otherwise

If there is a Loading Date, use Loading DAte, otherwise

Return a blank date

Here is how the data comes across in PowerBI currently:

I created what the results should look like in excel. The third column (column C) I'm trying to create using the measure.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Ashish_Mathur]

Hi,

In the ZLIP Table, write this calculated column formula

Date = IF(ISBLANK(ZLIP[Loading Date]),ZLIP[STO Load Date],ZLIP[Loading Date])

Drag this Date field to your visual.

Hope this helps.

[Anonymous]

Thanks, that seems like it should work, but my STO Load Date doesn't exist in the ZLIP table. Can I add a column to a table that relies on another table? See screen shot below of the DAX formula I tried. When I write the formula I get an error when trying to reference another table.

[amitchandak]

@Anonymous 

Bring the two dates in one column based on the id into one table.  You can have a measure but that will need context every time

Example

New column in Table 1 = maxx(filter(table2,table1[ID] = table2[ID] && table2[Filter]="Filter",table2[Date])

New column in Table 1 = maxx(filter(table2,table1[ID] = table2[ID] && table1[project] = table2[project]),table2[Date])

Then using if or in march release, you have coalesce function create a new date column with both.