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.
Hello everyone,
I couldn't find a way to do this:
I have a table with 2 columns. The first column are some codes, which CAN be repeated. The 2nd column is a binary unmber (0/1).
Example:
CODES NUMBER
codeA 1
codeA 1
codeA 0
codeA 0
codeB 1
codeC 0
codeC 0
I need to make a 3rd column, with this logic: "If (at least one of the NUMBERs of a code is 1, then1, else 0)". So in the 3rd column, all the registers of codeA would be 1 because there are 2 of the NUMBERs of codeA that is 1. So the result would be:
CODES NUMBER 3rdColumn
codeA 1 1
codeA 1 1
codeA 0 1
codeA 0 1
codeB 1 1
codeC 0 0
codeC 0 0
Thanks in advance!
Solved! Go to Solution.
Hi @aszpic,
Firstly, I will set Number column to text to prevent it sum/count that column in table/matrix
Create Calculated column:
3rd = IF(CALCULATE(COUNT(Sheet1[Number]),filter(all(Sheet1),Sheet1[Number]="1" && Sheet1[CODES ]=EARLIER(Sheet1[CODES ]) ))>0,"1","0")
Earlier method to get current row, All method to select and compare in all rows in table
Hi @aszpic,
Firstly, I will set Number column to text to prevent it sum/count that column in table/matrix
Create Calculated column:
3rd = IF(CALCULATE(COUNT(Sheet1[Number]),filter(all(Sheet1),Sheet1[Number]="1" && Sheet1[CODES ]=EARLIER(Sheet1[CODES ]) ))>0,"1","0")
Earlier method to get current row, All method to select and compare in all rows in table
thanks a lot! It threw me an error because it can't compare numbers with text values, but with a little variation it worked perfectly.
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |