Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
Ensure that there is a Many to One relationship from the Count table to the Product table. To your visual, drag Product from the Product table. Write this measure
Count = coalesce(countrows(Count),0)
You may now filter the measure on 0.
Hope this helps.
Hi @Ashish_Mathur,
Thank yo for your reply. I am getting a cardinality error for many to one relationship. Any ideas? Thanks again.
Ensure that there are no duplicates/blanks in the Products table.
hi @Oros ,
in power query, you could possibly do a full outer join. Filter the key columns on either table = blank and do a count.
create 3 blank queries and copy paste the code below into the advanced editor:
INPUTS:
count
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwI8HFV0lHySsxTitWJVnJy9ANCoIBLajJYwD/I0c8dpMI/uQQs4B7qGAZS4JdfphQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Date Counted" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date Counted", type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Product", Text.Upper, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Product", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Product", Text.Clean, type text}})
in
#"Cleaned Text"
product
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwI8HFVitWJVnJy9ANCMNM/yNHPHSLqC2T5g1lBjsEBTq5BQZGhYG5wSJBjOJivFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Product", Text.Upper, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Product", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Product", Text.Clean, type text}})
in
#"Cleaned Text"
output (get the products in product table but not in the count table) :
let
Source = Table.NestedJoin(Table, {"Product"}, Count, {"Product"}, "Count", JoinKind.LeftOuter),
#"Expanded Count" = Table.ExpandTableColumn(Source, "Count", {"Product", "Date Counted"}, {"Product.1", "Date Counted"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Count", each ([Product.1] = null)),
#"Counted Rows" = Table.RowCount(#"Filtered Rows")
in
#"Counted Rows"
Hi @adudani ,
Thank you for your quick reply. Should I replace the 'Product' with the actual product name table?
yes.
or you could copy the steps after source if you'd like.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
89 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
112 | |
110 | |
72 | |
72 |