Forum Discussion

W2SANC's avatar
W2SANC
Icon for Helper II rankHelper II
5 months ago
Solved

Help With Semantic Model - Different Sums & Counts for Sales in Specific Counties From Total Sales

Hi! I have a sales table where each row is a sale. Each sale has location data - city/town, county, state, zip, census tract (althought some are missing census tracts). I have a second data source with the list of all counties (columns are county, county FIPS code, state, state FIP code, and "county, state" , county & state FIPs code, and then the full census tract #. I have third source with similar columns  to the second source for all counties in the US with an additional column of "y/n" to indicate if they meet a certain federal definition we track. 

 

I merged the two queries (all counties info, and then the designation status (Y/N)) to add the status column to all counties. 

 

I have attempted several measures to filter to get both count of sales and $ amount of sales that have that "Y" status for specific counties, but can't get it to work in visuals. 

 

If I use the measurements or apply the designation filter to the total count of sales or the total amount of sales to a card visual, it changes from the total # or $ to "---"). So it isn't showing an error, but it isn't working either. I am not sure what to try next. 

 

Any help would be appreciated! I've copied below the dax queries I've tried so far in various measurements. I've also attached a picture of the model so far with color coded relationships. 

 

 = CALCULATE(SUM('Sales_Table'[Loan Count]),'DesignationStatusforAllCounties'[XXX Status]="Y") 
 
= CALCULATE(SUM(Sales_Table[Loan Count]),'DesignationStatusforAllCounties'[XXX Status]="Y")
 
= CALCULATE(SUM('Sales_Table'[Loan Count]),'Sales_Table'[MergedCountyState] IN {"Y"},'DesignationStatusforAllCounties'[XXX Status])
 
= SUMX(FILTER( 'Sales_Table'  
            ,  RELATED('DesignationStatusforAllCounties'[MergedCounty_State])  
               <>"N"  
             )  
     ,'Sales_Table'[MergedCountyState])
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    5 months ago

    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.

10 Replies

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    5 months ago

    Hi 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.

    • W2SANC's avatar
      W2SANC
      Icon for Helper II rankHelper II
      5 months ago

      Hello! 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's avatar
        v-hashadapu
        Icon for Community Support rankCommunity Support
        5 months ago

        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's avatar
      W2SANC
      Icon for Helper II rankHelper II
      5 months ago

      unfortunately i cannot share the file outright, but I did add the reply above with a new schreenshot ^^^

  • krishnakanth240's avatar
    krishnakanth240
    Icon for Super User rankSuper User
    5 months ago

    Hi W2SANC 

     

    Issue either from Data Modelling or Cross Filter Direction not DAX. Can you share a pbix file over gdrive link with the access.

    • W2SANC's avatar
      W2SANC
      Icon for Helper II rankHelper II
      5 months ago

      unfortunately i cannot share the file outright, but I did add the reply above with a new schreenshot ^^^