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.
I am trying to use column from one table as a slicer, to filter different table. Filter should be check if the value contains any of the selected values.
I wanted to use count to filter if the count is greater or equal to one.
I have the following measure that works if single value is selected but I am struguling to find a way to solve it when multiple values are selected:
CALCULATE (
COUNT ( Data[Countries] ),
FILTER ( Data, CONTAINSSTRING ( Data[Countries], VALUES( ( Slicer[Country] ) ) )
))
Solved! Go to Solution.
Hi @Krcmajster ,
Agree with @amitchandak .
Please transform the table to:
Here is the whole M syntax
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvR1DfJ0dgzWUXD1dXVUitUBCgU4OmPl6ijAVIMl4CqgcmB1CBWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Countries = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Countries", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Countries", "Countries - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Countries - Copy", "Country"}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Country", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Country"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Country", Text.Trim, type text}})
in
#"Trimmed Text"
Then createa measures:
If contains = IF(MAX('Data'[Country]) in ALLSELECTED(Slicer[Country]),1,0)
Filter =
var _t=ADDCOLUMNS('Data',"Flag",[If contains])
return SUMX(FILTER(_t,[Countries]=MAX('Data'[Countries])),[Flag])
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Krcmajster ,
Agree with @amitchandak .
Please transform the table to:
Here is the whole M syntax
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvR1DfJ0dgzWUXD1dXVUitUBCgU4OmPl6ijAVIMl4CqgcmB1CBWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Countries = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Countries", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Countries", "Countries - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Countries - Copy", "Country"}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Country", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Country"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Country", Text.Trim, type text}})
in
#"Trimmed Text"
Then createa measures:
If contains = IF(MAX('Data'[Country]) in ALLSELECTED(Slicer[Country]),1,0)
Filter =
var _t=ADDCOLUMNS('Data',"Flag",[If contains])
return SUMX(FILTER(_t,[Countries]=MAX('Data'[Countries])),[Flag])
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Krcmajster , I doubt searching multiple values as a table. Better to split the country in the table into multiple rows in power query
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |