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 Experts,
I'm stuck!
I have the data below, and need 'Result' column to show "yes" if,
Scode = BBB,
& IF icode matches another cell in icode &,
if Lcode matches both rows, &,
if Ocode on a matching row = anything but 0.
I also want 'Result 2' column to show the Ocode that matches on all 'Result' comlum "yes"...
I have shown the results below...
I hope this is possible! Thanks in advance PBI experts.
Scode | icode | Lcode | Ocode | Result | Result 2 |
BBB | X | ABC | 0 | yes | 123 |
BBB | C | ABC | 0 | yes | 123 |
BBB | V | ABC | 0 | no | 0 |
BBB | B | ABC | 0 | no | 0 |
BBB | X | ABC | 123 | yes | 123 |
BBB | C | ABC | 123 | yes | 123 |
BBB | V | ABC | 0 | no | 0 |
BBB | B | ABC | 0 | no | 0 |
BBB | X | CDE | 0 | no | 0 |
BBB | C | CDE | 0 | no | 0 |
BBB | V | CDE | 0 | yes | 456 |
BBB | B | CDE | 0 | yes | 456 |
BBB | X | CDE | 0 | no | 0 |
BBB | C | CDE | 0 | no | 0 |
BBB | V | CDE | 456 | yes | 456 |
BBB | B | CDE | 456 | yes | 456 |
Solved! Go to Solution.
I'm assuming that you want to calculate this as a calculated column, not a measure as from what I can see it probably only makes sense at the row level. If this is correct I think the following 2 expressions should work
Result Column = VAR _currentIcode = Table1[icode] var _currentLcode = Table1[Lcode] VAR _table = FILTER(Table1, Table1[icode] = _currentIcode && Table1[Lcode] = _currentLcode && Table1[Ocode] <> 0) return if( COUNTROWS(_table) > 0, "yes", "no")
Result2 Column = VAR _currentIcode = Table1[icode] var _currentLcode = Table1[Lcode] VAR _table = FILTER(Table1, Table1[icode] = _currentIcode && Table1[Lcode] = _currentLcode && Table1[Ocode] <> 0) return CONCATENATEX( _table, [Ocode], ",")
I'm assuming that you want to calculate this as a calculated column, not a measure as from what I can see it probably only makes sense at the row level. If this is correct I think the following 2 expressions should work
Result Column = VAR _currentIcode = Table1[icode] var _currentLcode = Table1[Lcode] VAR _table = FILTER(Table1, Table1[icode] = _currentIcode && Table1[Lcode] = _currentLcode && Table1[Ocode] <> 0) return if( COUNTROWS(_table) > 0, "yes", "no")
Result2 Column = VAR _currentIcode = Table1[icode] var _currentLcode = Table1[Lcode] VAR _table = FILTER(Table1, Table1[icode] = _currentIcode && Table1[Lcode] = _currentLcode && Table1[Ocode] <> 0) return CONCATENATEX( _table, [Ocode], ",")
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 |
---|---|
110 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |