Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My data types have been set in Power Query for the fields Base 93 Price, Count and Each Price in the "codes" table and show the Sigma sign.
Once I apply the relationship to the "Sales" table (Material to Item# as a one (material) to many (item#), this happens:
It pulls the data correctly, but the fields in the "codes" table are not showing the "sigma" - even thought they're coded as whole numbers or currency and I can't use them in a calculated column or get a total of the Count field (count is the "eaches" in a pack of the product - so either a dozen, 2 dozen or 3 dozen).
Can someone let me know what I'm doing wrong because my head hurts trying to figure this out.
Thanks much!
Solved! Go to Solution.
Hi @PaulDBrown ,
Like everything else in Power BI, there are no absolutes, but a few things about key fields vs value fields:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIf 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
@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!
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown ,
Like everything else in Power BI, there are no absolutes, but a few things about key fields vs value fields:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
Thanks for that very comprehensive explanation. It all makes sense of course.
I particularly like your "best practice" of converting Numeric IDs to text.
Again, thank you for taking the time to offer the explanation. Much appreciated!
Best,
Paul.
Proud to be a Super User!
Paul on Linkedin.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI changed the name from Count to ItemCt and it let me do the calculation even though the Sigma wasn't there. So you were right with respect to that just being a visual thing - WHICH THEY SHOULD FIX - but on top of that I needed to change the name of the field. I really should have not named it that - no need for that kind of confusion!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |