Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi so i've got one column in my table that contains sector infomation much like the below.
Sectors Covered |
Consumer Discretionary;Consumer Discretionary Products;Automotive |
Health Care;Health Care Facilities & Svcs |
Consumer Services;Insurance;Consumer Discretionary;Financials;Consumer Discretionary Services;Automotive |
Consumer Discretionary Products;Consumer Discretionary Services;Retail & Whsle - Discretionary;Consumer Staple Products;Retail - Consumer Staples |
I need some osrt of output, like a table that would count all occruances of each in the table
Sector | Count |
Consumer Discretionary | 3 |
Consumer Discretionary Products | 1 |
Automotive | 2 |
Financials | 1 |
Solved! Go to Solution.
Hi @DarylRob ,
According to your description, I modify the sample. There's a Revenue column in the fact table. If rows in the Sector Covered contains any value in the Sector table, sum the Revenue with these rows.
In the sample, you can see , the second row "Health Care;Health Care Facilities & Svcs" doesn't contains values in Sector table, so the result should be 8(1+3+4). Here's my solution, create a measure.
Sum =
VAR _T =
ADDCOLUMNS (
'Table',
"Flag",
IF (
COUNTROWS (
FILTER (
VALUES ( Sector[Sector] ),
SEARCH ( 'Sector'[Sector], 'Table'[Sectors Covered],, 0 )
)
) > 0,
1
)
)
RETURN
SUMX ( FILTER ( _T, [Flag] = 1 ), [Revenue] )
Get the result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DarylRob ,
According to your description, I create a sample, there're two tables
Table:
Sector table:
Here's my solution, create a measure.
Count =
VAR _T =
ADDCOLUMNS (
'Table',
"Sector",
IF (
CONTAINSSTRINGEXACT ( 'Table'[Sectors Covered], MAX ( 'Sector'[Sector] ) ),
1
)
)
RETURN
SUMX ( _T, [Sector] )
Get the correct result:
Note: In your sample, there're two Consumer Discretionary Products, so the count should be 2.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This perfect thank you,
Just on this as well, is there a way to tweak it so instead of counting the number of occurances we could Sum the total of another column in the original table, say if there was a "Revenue" column. We could then sum the revenue for all those that contain a specific sector?
Hi @DarylRob ,
According to your description, I modify the sample. There's a Revenue column in the fact table. If rows in the Sector Covered contains any value in the Sector table, sum the Revenue with these rows.
In the sample, you can see , the second row "Health Care;Health Care Facilities & Svcs" doesn't contains values in Sector table, so the result should be 8(1+3+4). Here's my solution, create a measure.
Sum =
VAR _T =
ADDCOLUMNS (
'Table',
"Flag",
IF (
COUNTROWS (
FILTER (
VALUES ( Sector[Sector] ),
SEARCH ( 'Sector'[Sector], 'Table'[Sectors Covered],, 0 )
)
) > 0,
1
)
)
RETURN
SUMX ( FILTER ( _T, [Flag] = 1 ), [Revenue] )
Get the result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
you can use split column by delimiter transformation. select the delimiter. here probably semicolon, each occurrence, and in advanced options split to Rows.
then you get a table of all occurrances.
use matrix visual and to rows put sectors and in values count of sectors.
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVCxCsJADP2V0NlCcXC5SSpFN7GDQ+0QzkAD155ccgX/3kO0SLF0C+/lveS9pslKP0jsKcCBxQZS9gOGp/kPwzn4e7QqZh/V9155pKzdNNmR0GkHJQYyPzNUaNmxMgncYlFsd1CPVt6S6UJNYWRLYk4JCDhYWrhuKh4Szehk6b/Ja/bfWpw1uwspsvtmuHbiCPKlymrFR+In8484h9lG6qF9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sectors Covered" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sectors Covered", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Sectors Covered", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sectors Covered"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sectors Covered", type text}})
in
#"Changed Type1"
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |