Counting occurrences in a text with filters on table

[RobertoAnd]

hi all,

I have a table like this (Table1):

Agenzia Zona
AAA      PC
BBB       PV
CCC       NO VC LC MI
AAA      MI BG
AAA      PC PR
CCC       MI
DDD      SP GE
FFF        SP GE

with a calculated column 

Count = COUNTROWS(FILTER(Table1,CONTAINSSTRING(Table1[Zona],Table2[Prov]))

I have this risult in Table2:

Prov Count
MI       3
NO      1
PC       2
PV       1
SP       2

Now: if I filter Agenzia in Table1, Table2 remains the same above.

For example filtering Agenzia = "AAA" I'd want:

Prov Count
MI       1
NO      0
PC       2
PV       0
SP       0

How can I do this?

Thank you

Roberto

[RobertoAnd]

what can I say? THANK YOU!!

[lbendlin · Accepted solution]

First step is to bring the data into usable format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TctBCsAgDETRq4SsvYQZGxFqGyy4Ee9/jVpbqMs3/GmNvffs2MDdNRaRB3UCwMBxUgXtoJzm+vY5kcTFBrKyvL44hDBwGcVtWlV/9xs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Agenzia = _t, Zona = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Zona", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Zona")
in
    #"Split Column by Delimiter"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Next step is to create a dimension tables with your agency names and zones. You can do that in Power Query or in DAX

Table2 = VALUES(Table1[Agenzia])
Table3 = VALUES(Table1[Zona])

Then link them in the data model

And finally create your visuals.  Select "Show items with no data" or add zero to the count measure.