I'm hoping for some help here as I am pulling my hair out. I've been trying to showcase Sales Forecast and Weekly sales by team in a report I've been working on. For some reason one of the teams is not being detected properly. In the Query Editor it clearly shows 26 entries for this team when filtered. Then when I'm adding elements to the Report it only shows 1.
I am trying to show Total for this team as a "Card" visualization. I am lost here and I'd love some help if possible.
Things I've Checked:
- Went over all entries to ensure exact naming match
- Reviewed my Excel file with the Raw Data and confirmed no blanks
- Checked the Query Tool and found 26 Entries for this team
- Made sure all data feilds were set (dates as dates, currency as currency, etc)
as @parry2k says, if you have orphan records (records in your fact/data table with no matching record in the lookup/dimension table) you will get this behaviour.
to check for this, create a table on a new report. Place a slicer on the dimension table and select the blank record. Add the foreign key from your data table to the visual. Add any other column in the data table to the values section (eg count of a text column is good). This will show you teh orphan values
Another possibility is that you have data in a column that is incompatible with the data type of your column, e.g. decimal numbers in a column with data type "Whole Number" (= Int64.Type), which is possible e.g. if you provided a data type with Table.AddColumn.
In this situation, the data will be visible in the Query Editor, but won't load into the data model.
Recently I rased an issue for this.
Microsoft explained that the column types are advisory and don't guarantee that the actual values will have this type.
Thanks for the insights gurus. I have relationships and I was under the impression I had a handle on how to use them but perhaps I'm going to have to go back and do some more practical research and work. I've been trying to use them like "baked in VLOOKUPs to change computer names to friendly names. The system uses the format below and I wanted all the reports to show as First Names & Last Name; Ben Decham in my example.
|Ben Descham(firstname.lastname@example.org) (email@example.com)|
Am I wrong in thinking that I can create a sheet (lets call it Rep Name) with the unique system names for all the sales reps in the 3 report sheets (Forecast, Budget, New Sales) and map it to their friendly name as with Ben above? My Rep Name sheet has System Name, Friendly Team and Sales Rep Name. I think the Team name is the only value that would not be unique as many unique users are assigned to each team.
This is showing me how embarrsing my skill level is at PowerBI. Just when I thought I was making good headway I hit the wall. Here is the screen grab of my spaghetti relationship tables. I've also included the report layout in case that helps.
You can do what you say, but it is not the best approach. You shold make these mapping changes during data load rather than after data load.
Read my article here http://exceleratorbi.com.au/shaping-modelling-power-bi/