Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Greetings,
My data set concatenates multiple field values into a single cell where they appear in the same record. However, there are two variants of each value.
Trying to create a count measure of rows that contain one value, another value or both. For instance, a five count of rows that contain Blue Car or Blue Truck, and omit Blue Van.
Record No. | Product |
875534 | Blue Truck |
875535 | Blue Car, Blue Truck |
875536 | Blue Van |
875537 | Red Car, Blue Truck |
875538 | Red Car, Red Van |
875539 | Blue Car, Red Truck |
875540 | Blue Car |
I've been looking for hours and found a filter query that would return the cells containing one value (rows 1 and 7), but none of the combined ones. Ive been working with CONTAINSSTRING, but can't fugure out how to add add second variable.
Thank you!
Solved! Go to Solution.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hi @stvitus
First you need to manually create a disconnected table that contains all the unique values ("Blue car", "Ref Truck", ect.) to be used as a slicer. Then the CountMeasure would be
CountMeasure =
COUNTROWS (
FILTER (
'Table',
NOT ISEMPTY (
FILTER ( SlicerTable, CONTAINSSTRING ( 'Table'[Product], SlicerTable[Value] ) )
)
)
)
you can also place it in the filter and filter is 1 or is not 0
User | Count |
---|---|
51 | |
27 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
23 | |
22 |