Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello community team,
I am using below ConcatenateX formula for the purpose to join multiple values in pivot if there is any dual values for the same variable.
=CONCATENATEX(DISTINCT(Table1[Mode]), Table1[Mode] ,", ")
Although there is one limitation is that whenever there is no second value or blank value so it still return a comma and a space. For Example: "ModeValue, " and sometimes if initial value is blank then it returns ", ModeValue"
I would like if there is no second value/ blank value or initial value is blank so I want the individual value to be returned without comma and the space. For Example: "ModeValue"
Thanks in advance
Dear John, Thanks for the input.
when i apply the same formula, there is a bit of change in the output as below. If you notice so, the values on the left are correct although when I add your formula in Excel, the output is uniform for all months (mentioned as TestFormula).
Thanks
You could use
My Measure =
CONCATENATEX (
DISTINCT (
FILTER ( ALLSELECTED ( Table1[Mode] ), NOT ISBLANK ( Table1[Mode] ) )
),
Table1[Mode],
", "
)
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |