Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm still new to Power BI, so any help is appreciated.
I have two tables, one with all raw data for who worked on what product like so (TableA):
Product | Person Who Worked on it |
ABC-ABBA | Bob |
DEF-ABBA | Steve |
I have a second table that dictates which of the products are harder to work on (TableB):
Hard Strenous Product |
DEF |
Is there a way to pattern match products from TableA with known hard streneous products from TableB and create a new column for it on TableA? I'm hoping for an "IF" statement where, if it's a hard product, return "HardProduct" and if it's not, then return "NotAHardProduct" within the cell values on the same rows like below:
Product | Person Who Worked on it | Product Type |
ABC-ABBA | Bob | NotAHardProduct |
DEF-ABBA | Steve | HardProduct |
I know I can do this on M but within "M" I can't refer to the TableB from TableA when creating a custom column within TableA.
Solved! Go to Solution.
Hi @OrionTL
Please follow sample file with the solution https://www.dropbox.com/t/wi6ldBAjf4fGEUkX
TableC =
ADDCOLUMNS (
TableA,
"Product Type",
IF (
ISEMPTY (
FILTER (
TableB,
CONTAINSSTRING ( TableA[Product], TableB[Hard Strenous Product] )
)
),
"Not A Hard Product",
"Hard Product"
)
)
Hi @OrionTL
Please follow sample file with the solution https://www.dropbox.com/t/wi6ldBAjf4fGEUkX
TableC =
ADDCOLUMNS (
TableA,
"Product Type",
IF (
ISEMPTY (
FILTER (
TableB,
CONTAINSSTRING ( TableA[Product], TableB[Hard Strenous Product] )
)
),
"Not A Hard Product",
"Hard Product"
)
)
Thank you @tamerj1 !
I was hoping to make it upon the already existing tableA but creating a new one is fine too.
I appreciate your help!
@OrionTL
Ofcourse you can. Actually it is the same code of the column created inside ADDCOLUMNS
ProductType =
IF (
ISEMPTY (
FILTER (
TableB,
CONTAINSSTRING ( TableA[Product], TableB[Hard Strenous Product] )
)
),
"Not A Hard Product",
"Hard Product"
)
Hi @OrionTL
that would be much easier using dax calculated table or a measure in your power bi report. Would you consider that?
I have tried using a DAX calculatedtable like so
HType =
VAR Matches =
CALCULATETABLE
(
GENERATE('HardProducts',
FILTER
(
Product_BuyOff,
SEARCH
(
[Assemblies],
[Part Number],
1,
0
)
>0)
)
)
RETURN
CONCATENATEX(
Matches,
"LH",",")
but I just get blank cells for the non-matching ones and "LH" for the matching ones. I can't figure out how to modify that code to make the blanks return another value when it's not matching.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |