The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a dataset that contains users in and all users. I want to be able to compare these columns and return a column that shows users that are in
Expected Result:
Solved! Go to Solution.
@Anonymous solely with a dax measure.
_missingValues =
VAR _1 =
ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
VAR _2 =
GENERATE (
_1,
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
"Words", PATHITEM ( [_allUsers], [Value], TEXT )
)
)
RETURN
CONCATENATEX (
FILTER (
_2,
[Users off] <> [Words]
&& CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()
),
[Words],
",",
[Words]
)
And a calculated column
Column =
VAR _1 =
ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
VAR _2 =
GENERATE (
_1,
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
"Words", PATHITEM ( [_allUsers], [Value], TEXT )
)
)
RETURN
CONCATENATEX (
FILTER (
_2,
[Users off] <> [Words]
&& CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()&&'Table'[Index]=EARLIER([Index])
),
[Words],
",",
[Words]
)
PBIX is attached
@Anonymous solely with a dax measure.
_missingValues =
VAR _1 =
ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
VAR _2 =
GENERATE (
_1,
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
"Words", PATHITEM ( [_allUsers], [Value], TEXT )
)
)
RETURN
CONCATENATEX (
FILTER (
_2,
[Users off] <> [Words]
&& CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()
),
[Words],
",",
[Words]
)
And a calculated column
Column =
VAR _1 =
ADDCOLUMNS ( 'Table', "_allUsers", SUBSTITUTE ( 'Table'[All Users], ",", "|" ) )
VAR _2 =
GENERATE (
_1,
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [_allUsers] ) ),
"Words", PATHITEM ( [_allUsers], [Value], TEXT )
)
)
RETURN
CONCATENATEX (
FILTER (
_2,
[Users off] <> [Words]
&& CONTAINSSTRING ( [Users off], [Words] ) = FALSE ()&&'Table'[Index]=EARLIER([Index])
),
[Words],
",",
[Words]
)
PBIX is attached
@Anonymous did you try the above?
@Anonymous , A new column in power query
List.RemoveItems(Text.Split([All User],","),Text.Split([All User],","))
Hi @amitchandak sorry my current columns are via a DAX Column. Therefore this would need to be done in DAX
Hi @Anonymous ,
Agree with @ amitchandak, it is better to use Power Query to achieve it because the string comparison is strictly defined in DAX and split columns is too complex.
Since the two user columns have been created by DAX, you can consider using the simliar logic to create them in Power Query first and refer this simliar thread to compare string columns to find the difference in Power Query:
Comparing String in two columns for differences
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
123 | |
111 | |
78 | |
74 |