Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Experts, could you please help in finding the solution for my requirement
I have 2 tables with 40K rows in each table,
I have slicer with package column from Table A and i have a table visual from Table B,
no relationship between Table A and Table B due to no common key value, but TableA[package] is a substring of TableB[Command]
my requirement is, when i filter TableA[package] using slicer, the related data should filter in tableB.
how can i solve this?
I tried Calculated table using CONTAINSSTRING Function
I tried Search function and LOOKUPVALUE function
nothing worked out for me.
here is data looks like
Table A:
Package |
getHelp |
getSales |
migarteData |
Table B: (may contain duplicates in command column)
Service | Command |
Service1 | C:\file\source\getHelp\folder\aaa |
Service2 | \\filegroup\folder\getSales\ |
Service3 | service.ex migrateddata from 2 |
Service4 | D:\file\source\getHelp\folder\aaa |
Expected Output
Service | Command | Package |
Service1 | C:\file\source\getHelp\folder\aaa | getHelp |
Service2 | \\filegroup\folder\getSales\ | getSales |
Service3 | service.ex migratedata from 2 | migratedata |
Service4 | D:\file\source\getHelp\folder\aaa | getHelp |
Solved! Go to Solution.
@smoka3 , Create a new column in TableB
maxx(filter(TableA, CONTAINSSTRING(TableB[Command], TableA[Package])),TableA[Package])
@smoka3 , Create a new column in TableB
maxx(filter(TableA, CONTAINSSTRING(TableB[Command], TableA[Package])),TableA[Package])
Thanks You!
@smoka3 - So basically this is a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534.
Kind of.
In your case, something like this:
Measure =
VAR __Command = MAX('Table B'[Command])
VAR __Table = ADDCOLUMNS(ALL('Table A'),"Include",FIND([Package],__Command,,0))
RETURN
MAXX(FILTER(__Table,[Include]>0),[Package])
Package... LOL...
Thank you!
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |