Forum Discussion
Data type issue when applying a relationship
Hi PaulDBrown ,
Like everything else in Power BI, there are no absolutes, but a few things about key fields vs value fields:
- Key fields should be unique on the 1 side. I'm ignoring the complexities of the many to many relationships.
- Key fields should generally be hidden on the many side. I've seen a lot of problems in reports where the key field was used in a visual in a FACT table vs the DIM table. Doing this with dates will prevent time intelligence functions from working.
- As noted by the OP, the sigma sign is vanishing, and while that appears on the face of it to be a visual issue, I don't know what SSAS is doing underneath when that field is used as a filter relationship.
- While I am sure someone will come up with a scenario, what possible mathematical use would a unique value have in a table besides counting? If you are just counting, you can count the rows. But you shouldn't be dropping that field into a visual to create an implicit measure, not from the FACT or DIM table. You could drop it in a table as a column or matrix as column/row as you would a part number, but not in values.
- Unless it is a unique ID generated by SQL or other database, I almost always convert numeric IDs to text before using them with filter relationships. It ensures they don't break if any alpha chars get introduced. Part numbers, employee numbers, etc.
I would just recommend creating a duplicate column in Power Query, then using that. Your "many" key field should be hidden anyway, so wouldn't cause confusion.
If the sigma is not showing up, then they are either coming in as "ANY" from Power Query, which is when the field has ABC/123 as the data type (always a bad idea), or they are formatted as text.
Formatting data in DAX by clicking on it is formatting only and will not change the data type. Power Query can change the type, which is what you need to do.
I have the data type set in Power Query:
What's happening is that once I apply the relationship, the Sigma goes away. If I delete the relationship, it comes back.
It is just a visual thing, and I'm not sure why. I set to tables up with integers and the sigma was there. I then joined as a 1:Many relationship and the sigma's vanished, but I was still able to summarize, add calculated columns, and create measures on those columns.
That said, your key fields in relationships should not be used in calculations. Create a duplicate column in Power Query and use those for calcs.
I'll dive back into it but it wasn't letting me do a calculated column using the "count" field. Thanks for the feedback!
Ok. Let us know how it goes. Mark my answer as Solved if this is the solution so others can find this, or post back with other info.
edhans wrote:
"your key fields in relationships should not be used in calculations. Create a duplicate column in Power Query and use those for calcs."
edhans
I wasn't aware of a potential problem with using key fields in calculations. What are the risks? (I'm trying to think of examples of when I would use a key field in a calc, but I am only coming up with using them in FILTER expressions....). But just in case...Thanks!
Hi PaulDBrown ,
Like everything else in Power BI, there are no absolutes, but a few things about key fields vs value fields:
- Key fields should be unique on the 1 side. I'm ignoring the complexities of the many to many relationships.
- Key fields should generally be hidden on the many side. I've seen a lot of problems in reports where the key field was used in a visual in a FACT table vs the DIM table. Doing this with dates will prevent time intelligence functions from working.
- As noted by the OP, the sigma sign is vanishing, and while that appears on the face of it to be a visual issue, I don't know what SSAS is doing underneath when that field is used as a filter relationship.
- While I am sure someone will come up with a scenario, what possible mathematical use would a unique value have in a table besides counting? If you are just counting, you can count the rows. But you shouldn't be dropping that field into a visual to create an implicit measure, not from the FACT or DIM table. You could drop it in a table as a column or matrix as column/row as you would a part number, but not in values.
- Unless it is a unique ID generated by SQL or other database, I almost always convert numeric IDs to text before using them with filter relationships. It ensures they don't break if any alpha chars get introduced. Part numbers, employee numbers, etc.
I would just recommend creating a duplicate column in Power Query, then using that. Your "many" key field should be hidden anyway, so wouldn't cause confusion.
