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,
i'm trying to find columns of 1 table on a 2nd table, but i'm struggling because i can't seem to use distinct or values with text operators (2nd column can be slightly different, as you'll see), and i also was trying to use addcolumns and summarize in a variable but i don't know how to use it after on the Calculate's Filter.
So what i pretend is to count table1\column1 in table2\column1, the detail is that :
- it could not exist, as AA-00004
- it can exist exactly equal or with a variation on the string (AA-00001 with AA-00001 and AA-00001--1)
Solved! Go to Solution.
@Anonymous
Then use
=
SUMX (
VALUES ( table1[Column1] ),
IF (
SUMX (
VALUES ( table2[Column1] ),
IF ( CONTAINSSTRING ( table2[Column1], table1[Column1] ), 1 )
) > 0,
1
)
)
Thanks for your help @tamerj1 , it seems OK in my real PBIx, by some reason in the PBI POC i created, with the 2 tables directly created in PBI, i was getting that message error.
i was trying now to do something more similar to you, as the idea is just to count once, but:
1) shouldn't the 1st SUMX be done over Table1, as we want to count from there?
2) can't i simply create a measure with the code you kindly shared and drag it to the screen? An error is appearing.
Thanks and Regards
Hello @tamerj1 ,
i was trying now to do something more similar to you, as the idea is just to count once, but:
1) shouldn't the 1st SUMX be done over Table1, as we want to count from there?
2) can't i simply create a measure with the code you kindly shared and drag it to the screen? An error is appearing.
Thanks and Regards
The first Values is table1 the 2nd values is table2. Please copy the same code in my last reply
Hi @Ricardo77
you can try
=
SUMX (
VALUES ( table2[Column1] ),
SUMX (
VALUES ( table1[Column1] ),
IF ( CONTAINSSTRING ( table2[Column1], table1[Column1] ), 1 )
)
)
Hello @tamerj1 , thanks for your help.
I think it's working but just failing in one detail: i need it to count on table1, so when values are found they just are counted once , as AA-00001 and AA-00005 .
Please clarify further
If you look to the codes in my example image it should return total of 4 from 1st table, as they are appearing 7 times, including variations, on 2nd table (forgot to place AA-00005 in blue, also).
So each entry that appears 1 or more times in 2nd table should should just count as 1.
Regards
@Anonymous
Then use
=
SUMX (
VALUES ( table1[Column1] ),
IF (
SUMX (
VALUES ( table2[Column1] ),
IF ( CONTAINSSTRING ( table2[Column1], table1[Column1] ), 1 )
) > 0,
1
)
)
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 |
---|---|
42 | |
23 | |
21 | |
16 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |