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.
Hi,
How to create New Column like this table?
topic_id | topic_pid | topic | New Column |
1 | 0 | Hardware | Hardware |
2 | 1 | Computer | Hardware / Computer |
3 | 1 | Printer | Hardware / Printer |
4 | 0 | Office365 | Office365 |
5 | 2 | Word | Office365 / Word |
6 | 2 | Excel | Office365 / Excel |
7 | 2 | Power Point | Office365 / Power Point |
Solved! Go to Solution.
@pisca , Try a new column like
New column =
var _max = maxx(filter(Table, Table[topic_pid]=0 && [topic_id] <earlier([topic_id])),[topic_id])
return
if([topic_pid]<>0 , maxx(filter(Table, Table[topic_pid]=0 && [topic_id] =_max),[topic]) &"/" & [topic],[topic])
Hi @pisca
You can use this DAX column pattern for the below result.
New Column =
VAR __getParentChildHierarchy = PATH( 'Table'[topic_id], 'Table'[topic_pid] )
VAR __selectFirstParent = PATHITEM( __getParentChildHierarchy, 1, INTEGER )
VAR __lookupTopicByID =
LOOKUPVALUE(
'Table'[topic],
'Table'[topic_id], __selectFirstParent
)
VAR __topic = 'Table'[topic]
RETURN
IF(
NOT __lookupTopicByID == __topic,
__lookupTopicByID & " / " & __topic,
__topic
)
This is if the topic_pid for office = 4, not 2 and you replace 0 with null so PATH() function works correctly
Hi @pisca
You can use this DAX column pattern for the below result.
New Column =
VAR __getParentChildHierarchy = PATH( 'Table'[topic_id], 'Table'[topic_pid] )
VAR __selectFirstParent = PATHITEM( __getParentChildHierarchy, 1, INTEGER )
VAR __lookupTopicByID =
LOOKUPVALUE(
'Table'[topic],
'Table'[topic_id], __selectFirstParent
)
VAR __topic = 'Table'[topic]
RETURN
IF(
NOT __lookupTopicByID == __topic,
__lookupTopicByID & " / " & __topic,
__topic
)
This is if the topic_pid for office = 4, not 2 and you replace 0 with null so PATH() function works correctly
Awesome, Thanks a lot..
@pisca , Try a new column like
New column =
var _max = maxx(filter(Table, Table[topic_pid]=0 && [topic_id] <earlier([topic_id])),[topic_id])
return
if([topic_pid]<>0 , maxx(filter(Table, Table[topic_pid]=0 && [topic_id] =_max),[topic]) &"/" & [topic],[topic])
Hi Amit, Nice solution. I've another challenge. Data Looks like the following.
NewsID | Parent | Child |
N1 | A | A1 |
N1 | A | A2 |
N1 | A | A3 |
N1 | B | B1 |
N1 | B | B2 |
N2 | C | C1 |
N2 | C | C2 |
N3 | C | C3 |
N3 | C | C4 |
N2 | D | D1 |
N3 | D | D2 |
N3 | D | D3 |
Now my output is being presented into a table visual - wherein for each newsID N1, N2 and N3 I've 1 row to display. Against each NewsID - I need to display the Parent and their child IDs concatenated like:
News | Linked Parent_Child |
N1 | A(A1, A2,A3); B(B1,B2) |
N2 | C(C1, C2); D(D1) |
N3 | C(C3, C4); D(D2,D3) |
Awesome, Thanks a lot..
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |