Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
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.
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.
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
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.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |