I created a measure using the SumX and Filter functions, see example below. It works for the most part, except for strings with spaces in a certain table named SalesDetail. I figure it's a problem with the relations of the data tables however I've deleted all relations to the table I am trying to filter and it still won't sum. Examples that work: 1. A/V Sales = SUMX(FILTER(PriceLines, PriceLines[ProductSegment]="A/V"),SalesDetail[Sales]) 2. Controller and I/O Sales = SUMX(FILTER(PriceLines, PriceLines[ProductSegment]="Controllers & I/O"),SalesDetail[Sales]) 3. Drive sales = SUMX(FILTER(SalesDetail, SalesDetail[WE_Subsegment]="Drives"),SalesDetail[Sales]) Example that does not work: 1. Test Sales = SUMX(FILTER(SalesDetail, SalesDetail[WE_Subsegment]="Controllers & I/O"),SalesDetail[Sales]) note*: "Controllers & I/O" is a value under WE_Subsegment that is spelled correctly both in the table and in the formula.
9 Comments
- Another possible clue... In the query editor, in a table that combines sales quarter excel spreadsheets, I tried using the replace values dropdown option on the WE_subsegments column to replace "Controllers & I/O" with "Controllers&I/O" as a workaround for this issue. After clicking the apply and close button in the query editor, the "Controllers & I/O" value remained the same in that table. Replaced value shows up in the applied steps field but had no effect. Does this indicate a data source problem or a table relationship problem? Just looking for help diagnosing this issue. PS: The excel spreadsheets are being accessed through One Drive.
- Another note: For the example formula that doesn't work, the value "Fire/Protection/Signal" works in the formula but "Wire Accessories" does not. This is what leads me to believe it's the spaces and not the special characters.
- Edit Queries > Advanced Editor will show you the generate code being used for a model
- Hello Clayton, I just tried this and can not reproduce the issue. I have the following formula: Measure = SUMX(FILTER(sheet1, Sheet1[WE_Segment]="Controllers & I/O"),Sheet1[Sales1]) Working against the following dataset Thanks Chuck WE_Segment Sales1 Controllers & I/O 1 Controllers & I/O 2 Other 3 Other 4 Other 2 Other 3
- Another piece of info... This formula works for a table named 'PriceLines' with the column 'ProductSegment' and search term 'Controllers & I/O'. 'PriceLines' is related one way to the table 'SalesDetail'. I've tried changing the relationship to two-way with no effect.
- I've tested all values under WE_subsegments and the text values that don't properly filter are values with spaces in them. Is there some way to view the code for the DAX formula in Power BI?
- I have finally found a solution to this problem. I had to copy and paste the term 'Controllers & I/O' from the original Excel file to get the formula to work. Even though the copy-and-pasted string was identical to the typed-in string, the display would only work with the copy-and-pasted string. To clarify, there were no extra characters in the copy-and-pasted string. Earlier I had tried copy and pasting 'Controllers & I/O' from the edit queries page with no success so this solution was a surprise.
- Clayton, Thanks for circling back with the solution! Really do appreciate it Chuck
- Status added:Completed
