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 am trying to get a new column for a table counting how many times each value appears on another table. Please see diagram below:
With normal tables, this is relatively easy, eg.
Solved! Go to Solution.
@lazarus1907
You can acheive the expected result by creating the following table:
MyTable =
VAR Tab1 = { 1, 2, 3, 4, 5, 6, 7, 8, 9 }
VAR Tab2 = { 4, 5, 7 }
RETURN
ADDCOLUMNS (
Tab1,
"Found",
VAR C1 = [Value]
RETURN
CALCULATE ( COUNTROWS ( FILTER ( Tab2, [Value] = C1 ) ) ) + 0
)
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@lazarus1907
Create the table as follows:
ADDCOLUMNS(
table1,
"found",
var c1 = table1[Value] return
CALCULATE(
COUNTROWS(table2),
table2[Value] = c1
)
)
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you for the reply, but Power BI does not accept table1[value] when table1 is a variable and not a normal table. That's the problem I had.
@lazarus1907
You can acheive the expected result by creating the following table:
MyTable =
VAR Tab1 = { 1, 2, 3, 4, 5, 6, 7, 8, 9 }
VAR Tab2 = { 4, 5, 7 }
RETURN
ADDCOLUMNS (
Tab1,
"Found",
VAR C1 = [Value]
RETURN
CALCULATE ( COUNTROWS ( FILTER ( Tab2, [Value] = C1 ) ) ) + 0
)
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Genius! Thank you very much!!!
I'd swear I've tried something very very similar to what you wrote, but I obviously did something wrong -probably when to filter.
@lazarus1907 , That ia an array, You have create table like
union(
ROW("Value", 1),
ROW("Value", 2),
ROW("Value", 3)
)
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |