concatenatex

22 Topics

CONCATENATEX the Column Header if 0 found in Row
I have a matrix table like this: Location Device1 Device2 Device3 Concatenate A 1 0 0 Device2, Device3 B 0 4 5 Device1 C 0 0 3 Device1,Device2 D 1 3 0 Device3 I'm trying to Concatenate the Header names (which comes from the DeviceNameShort column) in another column where the row contains a 0. The far right column that I named Concatenate is what I'm trying to achieve. Please note the Device names are all in one column called DeviceNameShort, but the matrix displays each device name in separate columns. I did this in a Measure and it lists all the devices needed for the whole report and it is very difficult to read, instead of keeping the few devices needed in each row. And it totally elimates the view of the device quantites. Any help would be awesome. Thanks! DevicesNeeded = IF( COUNTROWS(FILTER(IOH, IOH[DAX Product Count] = 0)) > 0, CONCATENATEX(IOH, IOH[DeviceModelShort], ", ") ) I tried using a calculated column, but I keep getting 'A circular dependency was detected. There are only two tables in the model. IOH and Hierarchy. Hierarchy has the Location and IOH has the DAX Product Count measure and DeviceModelShort column.
Solved
BrianNeedsHelp
11 months ago Place DAX Commands and Tips
757Views
0likes
4Comments
USING THE EXCEPT & CONCATENATEX FUNCTIONS TO OUTPUT A LIST OF EMPLOYEE NAMES
Hi, The Matrix below shows a count of employees by reporting week. I would like to be able to output a list of employee names when comparing 2 reporting weeks to identify what names appear in one week but not the other week. Ideally I would like to output this list via a drillthrough. I have tried using a combination of CONCATENATEX & EXCEPT as suggested by Greg_Deckler on a diferent thread, however, I haven't been successul in getting it to work. It makes sense to me that those functions would be part of the solution. Please refer to the screenshots below. Any advice would be greatly appreciated. 🤗 Regards, Karin Sample PBIX: https://drive.google.com/file/d/18igYaWq6okSlcbtWHAbmhFMWVU3ZpO_6/view?usp=drive_link Desired Output of Drillthrough Desired Output of Drillthrough
Solved
kmfitzy
2 years ago Place DAX Commands and Tips
1.4KViews
0likes
6Comments
DAX: FILTER and CONCATENATE UNIQUE values
I am trying to FILTER/LOOKUPVALUE and CONCATENATE the relevant UNIQUE values but right now, I am unable to get it to work. I can only do it without the values being UNIQUE. My current DAX formula: CONCATENATEX ( FILTER ( ALL ( Table2 ), Table2[Column1] = Table1[Column1] ), Table2[Column2], ", " ) The output from here goes like: Value1, Value2, Value2 My desired output is: (without any of the values being duplicated) Value1, Value2
Solved
Anonymous
2 years ago Place DAX Commands and Tips
9.9KViews
0likes
6Comments
Concatenatex with Distinct column values
Hi, I am summarizing data from one table into another. Part of the data in the original table is a column of type text which I generate a comma delimited summary string. Issues = var ft = FILTER(RELATEDTABLE(Data), [ID] = Data[ID] && Data[Result] <> "Pass" && Not ISBLANK(TRIM(Data[Issue]))) var issuessummary = CONCATENATEX(ft, Data[Issue], ",") return issuessummary This works perfectly but I realized that the return values can be duplicate, so a result value string for issuessummary could equal "ABC", "DEF", "ABC", "xyz". I would like to remove the duplicate in the return string to represent "ABC", "DEF", "xyz". Is there a way to accomplish that? Regards, Sunil
Solved
loganwol
2 years ago Place DAX Commands and Tips
28KViews
1like
6Comments
Concatenate text by group : EARLIER function not working
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
Anonymous
2 years ago Place DAX Commands and Tips
1.3KViews
0likes
4Comments
Remove Duplicate Values using CONCATENATEX and Direct Query
I am having trouble getting values from a Direct Query to appear correctly in a table using the CONCATENATEX fxn. I have tried multiple fxn setups and all present varying issues. Each row corresponds to a specific ID and each Finding (bmpObs) only appears once in the SQL dataset for each ID. The SQL dataset looks similar to this: ID#1 - value1 ID#1 - NULL ID#1 - value2 ID#1 - value3 ID#1 - NULL ID#2 - value1 ID#2 - NULL ID#2 - value4 ID#2 - value6 ID#2 - NULL ID#3 - value2 ID#3 - NULL ID#3 - value4 ID#3 - value5 ID#3 - NULL I'm trying to get a result that looks like: ID#1 - value1, value2, value3 ID#2 - value1, value4, value6 ID#3 - value2, value4, value5 Here are the DAX formulas I've tried using, in addition to others I can't remember at the moment. 1. List of BMP observations = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT(FindingsFacilityVisualList[bmpObs]) RETURN CONCATENATEX( DISTINCT( FILTER(FindingsFacilityVisualList, FindingsFacilityVisualList[bmpObs] <> BLANK() ) ), FindingsFacilityVisualList[bmpObs], ", " ) Results in duplicated values: ID#1 - value1, value2, value3, value1, value2, value3, value1, value2, value3 ID#2 - value1, value4, value6, value1, value4, value6, value1, value4, value6 ID#3 - value2, value4, value5, value2, value4, value5, value2, value4, value5 --------- 2. List of BMP observations = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('FindingsFacilityVisualList'[bmpObs]) RETURN CALCULATE(CONCATENATEX( DISTINCT( FindingsFacilityVisualList[bmpObs]), FindingsFacilityVisualList[bmpObs], ", " )) Results in a comma before the first value: ID #1 - , value1, value2, value3 ID #2 - , value1, value4, value6 ID #3 - , value2, value4, value5 --------- 3. List of BMP observations = CONCATENATEX( CALCULATETABLE( VALUES(FindingsFacilityVisualList[bmpObs]), ALLEXCEPT(FindingsFacilityVisualList, FindingsFacilityVisualList[bmpObs]), NOT ISBLANK(FindingsFacilityVisualList[bmpObs]) ), FindingsFacilityVisualList[bmpObs], ", " ) Results in incorrect values: ID#1 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#2 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#3 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#4 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#5 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 *ID#4 & #5 should not have values associated with them --------- 4. List of BMP observations = CONCATENATEX ( FILTER ( FindingsFacilityVisualList, LEN ( FindingsFacilityVisualList[bmpObs] ) > 0 ), FindingsFacilityVisualList[bmpObs], ", " ) Results in Error: Can't display the visual. --------- Any suggestions for alternate ways to get to my desired end result?
Solved
HoppeG76
2 years ago Place DAX Commands and Tips
3.4KViews
0likes
7Comments
DAX Function to Concatenate Distinct Values from a Dimension Grouped by a Different Dimension Column
Hello, Looking for the Community's help on this. I have the below data model. I want to create a column with DAX to concatenate product categories form DimProduct by the Employee to show all the categories that this employee sold. What I got from CONCATENATEX is repeated values and blank values of categories an Employee did not sell. Appreciate your help on this. Desired Outcome: Employee First Name Employee Last Name Categories Sold John Doe A, B, C, D Jane Doe B, C Lord Voldemort D Severus Snape A, C EDIT (26-10-2023): I am trying to create this a DAX column in the DimProduct table.
HRegnum
2 years ago Place DAX Commands and Tips
4.6KViews
0likes
8Comments
Concatenating comments based on unique values
Hello, I am relatively new to PowerBI so apologies the question is not framed well. I have the following dataset (simplified for ease). I want to combine the comments where the city and measure are the same. However, I need maintain all rows in the table. I have tried various CONCATENATEX variations but have had no luck. City Measure Comments Manchester Sales Good Manchester Sales Bad Manchester Expenditure Poor Manchester Expenditure Excellent London Sales Good London Sales Poor London Expenditure Poor London Expenditure Good My desired output is City Measure Comments on performance NewColumn Manchester Sales Good Good, Bad Manchester Sales Bad Good, Bad Manchester Expenditure Poor Poor, Excellent Manchester Expenditure Excellent Poor, Excellent London Sales Good Good, Poor London Sales Poor Good, Poor London Expenditure Poor Poor, Good London Expenditure Good Poor, Good Many thanks in advance.
djk123
3 years ago Place DAX Commands and Tips
531Views
0likes
1Comment
Select, Return and Conectate multiple fields based on criteria
I've been searching for awhile but can't quite find a query to return what I'm hoping to get. To keep it simple, I'm trying to write a query that can have multiple correct returns and, if I'm not asking for the moon, returns them all in the same row. Example: I have two tables like so: Assets ID Number Target Viscosity 1 5.9 2 8 3 23 Fluids Fluid ID Min Viscosity Max Viscosity 1 4.5 12 2 7 18 3 15 30 What I'd like to see is this: Assets ID Number Target Viscosity Fluid ID 1 5.9 1 2 8 1, 2 3 23 3 This pseudo-logic in my head is something along the lines of IF(AND(Assets[TargetViscosity] > Fluids[MinViscosity],Assets[TargetViscosity] < Fluids[MaxViscosity]), ...Return all possible matches in a single row with a deliminator..., 0) Anyone got any ideas?
Solved
denealst
3 years ago Place DAX Commands and Tips
806Views
0likes
2Comments
SUM if it is a number, otherwise CONCATENATEX
Hello, What I would like to do: To make a pivot table with values that are either numbers (Desired result: SUM) or text (Desired result: CONCATENATEX) Literally: If in column « 21-22_Rangement » ; the value is 'Cotes'; then I sum the values If in the column « 21-22_Rangement » ; there is another value ; then I concatenate the texts and separating them with a ", " My problem: I tried to create a new measure, but I must not give the right conditions. In the screenshot the formula used is : =CONCATENATEX(VALUES(_102_WALL_LAST_TEXT__2[Valeur]);[Valeur];", ") Test 1 : =SWITCH( TRUE(); _102_WALL_LAST_TEXT__2[21-22_Rangement]="'Cotes'";SUM(_102_WALL_LAST_TEXT__2[Valeur]); CONCATENATEX(VALUES(_102_WALL_LAST_TEXT__2[Valeur]);[Valeur];", ") ) But it is indicated to me that one cannot make a sum on texts. Test 2 : =SWITCH( TRUE(); _102_WALL_LAST_TEXT__2[21-22_Rangement]="'Cotes'";SUM(CONVERT(_102_WALL_LAST_TEXT__2[Valeur]),DOUBLE); CONCATENATEX(VALUES(_102_WALL_LAST_TEXT__2[Valeur]);[Valeur];", ") ) But it tells me that "Convert" is not a table, variable or function name I need you’re help please. Thank’s Clem212273
Clem212273
3 years ago Place DAX Commands and Tips
1.5KViews
0likes
5Comments