Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I am trying to create a measure in DAX to concatenate all CODES that have the same Group ID.
I have tried the CONCATENATEX function mentioned in many other discussion threads but it doesn't seem to function as there appears to be a problem with the function EARLIER which doesn't recognize any value.
This is the initial table:
Group Registration ID | SPN CODE |
578-24-3545895 | SM-CC |
578-24-3545895 | WR-SF |
578-24-3544933 | FU-TR |
578-24-3544933 | SM-AD |
578-24-3544933 | SM-CC |
578-24-3544933 | WR-SF |
578-24-3532081 | WR-SF |
578-24-3524650 | SM-DP |
578-24-3518403 | LP-MS |
578-24-3518403 | N/A |
The desired output should be as such:
578-24-3545895 | SM-CC | SM-CC; WR-SF |
578-24-3544933 | FU-TR | FU-TR; SM-AD; SM-CC; WR-SF |
578-24-3532081 | WR-SF | WR-SF |
578-24-3524650 | SM-DP | SM-DP |
578-24-3518403 | LP-MS | LP-MS; N/A |
I have tried this but it's not working and I have the following error :
CODES = CONCATENATEX(FILTER(SPN, SPN[Group Registration ID]=EARLIER(SPN[Group Registration ID])), SPN[SPN CODE], "; ")
I would appreciate any orientation
Thank you
Solved! Go to Solution.
If you are writing a measure, it will be a bit simpler than the code shown.
Follow the example here https://learn.microsoft.com/en-us/dax/concatenatex-function-dax?WT.mc_id=DP-MVP-4025372#example
Hi @Moulahoum ,
@JamesFR06 Good solution.
Here's what I need to add
According to mine using your data to test the dax you wrote in power bi, when I put your dax code into measure, he got the same error as you. But when you use the calculated columns, your dax code runs with the results you want. According to the documentation I checked, EARLIER succeeds if there is a row context prior to the beginning of the table scan. otherwise it returns an error.
Also, you can create a new table
Table =
ADDCOLUMNS(
SUMMARIZE('SPN', 'SPN'[Group Registration ID]),
"Codes",
CONCATENATEX(
FILTER('SPN', 'SPN'[Group Registration ID] = EARLIER('SPN'[Group Registration ID])),
'SPN'[SPN CODE],
"; "
)
)
Final output
EARLIER function (DAX) - DAX | Microsoft Learn
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Moulahoum ,
@JamesFR06 Good solution.
Here's what I need to add
According to mine using your data to test the dax you wrote in power bi, when I put your dax code into measure, he got the same error as you. But when you use the calculated columns, your dax code runs with the results you want. According to the documentation I checked, EARLIER succeeds if there is a row context prior to the beginning of the table scan. otherwise it returns an error.
Also, you can create a new table
Table =
ADDCOLUMNS(
SUMMARIZE('SPN', 'SPN'[Group Registration ID]),
"Codes",
CONCATENATEX(
FILTER('SPN', 'SPN'[Group Registration ID] = EARLIER('SPN'[Group Registration ID])),
'SPN'[SPN CODE],
"; "
)
)
Final output
EARLIER function (DAX) - DAX | Microsoft Learn
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Please find the measure :
If you are writing a measure, it will be a bit simpler than the code shown.
Follow the example here https://learn.microsoft.com/en-us/dax/concatenatex-function-dax?WT.mc_id=DP-MVP-4025372#example
This is the simplest function that solvedit. Thank you.
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |