Forum Discussion
Helper IIHelp With Semantic Model - Different Sums & Counts for Sales in Specific Counties From Total Sales
Hi W2SANC , Thank you for reaching out to the Microsoft Community Forum.
I think this is a data mismatch issue. Since No returns a value but Yes shows “--”, it means the filter is working, but none of the Y counties are matching any rows in Sales under your join key. I suggest you check a few Y counties from the designation table and verify that the exact same MergedCounty_State values exist in Sales, make sure to watch for spacing, casing and formatting differences. If they don’t match exactly or those counties aren’t present in Sales, you’ll get this behavior.
Community SupportHi W2SANC , Thank you for reaching out to the Microsoft Community Forum.
This looks less like a DAX issue and more like a model/filter propagation problem. Your measure should work if the designation table properly filters the sales table, but from your model it seems the relationship is indirect (likely via FIPS), so the filter on Y isn’t reaching Sales. Check that you have a valid relationship path or create a direct one using your county key or use TREATAS if a direct relationship isn’t possible.
If that doesn’t resolve it, could you share a bit more detail on your model, specifically the exact relationships (cardinality and filter direction), which columns you’re using as keys (e.g., MergedCountyState vs FIPS) and whether there are duplicates on the dimension side and anything else that is relevant? That will help us better understand the cause of the issue.
W2SANCHello! I current have the following relationships (different from screenshot above since I've been trying different models since then) - see screenshot below. I deleted my measures since I felt it was also not a DAX query error or need, but a model / filtering issue. When I create a card visual with total $ sales and add the designated counties as a filter from both the FIPs table and the Designation Status table, it shows as "--" - I'm not sure what else to try next.
- v-hashadapu
Hi W2SANC , Thank you for reaching out to the Microsoft Community Forum.
Your filter path of Designation Status -><- FIPS -> Sales, should work in theory, but you’re applying filters from both Designation and FIPS at the same time, while they represent the same grain (county). That creates an intersection of filters across two related dimension tables and if there’s even a slight mismatch like duplicates in FIPS, missing counties or non-identical keys, the result is an empty filter context, which shows as blank. This is exactly what your card is doing.
I suggest you stop using two tables to filter the same thing. Keep FIPS as your single dimension, merge the Y/N status into and relate FIPS (1) -> Sales (*) on MergedCounty_State. Then filter only on FIPS[Status] = "Y".
- W2SANC
I am still having trouble. I tried using the designation table mergedCounty_State to the Sales table mergedCounty_State - and in the card visual, if I add the designation status and filter by "no" - I get a dollar amount, but if I filter "yes", I still see "--"
Not sure what that means?