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"
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
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.
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.
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
Based on your description, I created data to reproduce your scenario.
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 ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!