Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have two tables:
Products table has a single column with one product per field:
List table has several columns but the column I'm interested in has fields that can have as little as one product to as much as 17, all separated with a comma and no spaces in between:
The issue I'm facing and need help with is to correctly count each product either, ideally as as a measure. I tried two measures but they both gave me an incorrect count of blanks; there are no blank values either table.
What I found is that both measures count only the *first* value they find in the List column and then continue counting subsequent rows:
How can I correctly count all values in each field from the List table and have zero "blank" counts?
Thanks!
Solved! Go to Solution.
@Xboraxe
Please delete or deactivate that relationship or use
Customer Products at M0 =
CALCULATE (
SUMX (
VALUES ( Products[Product] ),
SUMX (
VALUES ( Query1[M0_LIST] ),
INT ( CONTAINSSTRING ( Query1[M0_LIST], Products[Product] ) )
)
),
CROSSFILTER ( Query1[M0_LIST], Products[Product], NONE )
)
Hi @Xboraxe
If the same product is repeated multiple times inside the string and you want to consider that in the counting then please use
Customer Products at M0 =
SUMX (
VALUES ( Products[Product] ),
SUMX (
VALUES ( Query1[M0_LIST] ),
VAR String = Query1[M0_LIST]
VAR Items =
SUBSTITUTE ( String, ",", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
RETURN
COUNTROWS ( FILTER ( T2, [@Item] = Products[Product] ) )
)
)
Hi @tamerj1 , thank you. I tried using the first measure and still gave me the incorrect counts, using the second one gave me this result.
The same product is not repeated inside the string, but two clients can definitely have the same products but they would be listed in different rows.
Hi @tamerj1 , with your second measure I got a count of 1 for each product:
Also, the List table contains blank values/rows too, and as for active relationships I see that the Product and Lists table are connected in the Product[Products] column and the List[M0_LIST] column, one to many relationship. I don't know if this is causing the issue and hope this gives more clarity to my issue.
@Xboraxe
Please delete or deactivate that relationship or use
Customer Products at M0 =
CALCULATE (
SUMX (
VALUES ( Products[Product] ),
SUMX (
VALUES ( Query1[M0_LIST] ),
INT ( CONTAINSSTRING ( Query1[M0_LIST], Products[Product] ) )
)
),
CROSSFILTER ( Query1[M0_LIST], Products[Product], NONE )
)
Hi @Xboraxe
please try
Customer Products at M0 =
SUMX (
VALUES ( Products[Product] ),
SUMX (
VALUES ( Query1[M0_LIST] ),
INT ( CONTAINSSTRING ( Query1[M0_LIST], Products[Product] ) )
)
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
19 | |
14 | |
14 | |
11 | |
8 |