Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Sorry if the question title sounds confusing; I'm not really sure how to phrase it nicely. It's my first time doing this kind of work so due apologies if anything is phrased confusingly.
I have a table in PowerBI that combines both aggregated and individual data. An example dataset is as such:
I'd like to add a column that references the country each City/Country belongs to, as such:
But I don't really know how to find the nearest earlier row that contains "Country" as the categorical value. Is there any way to do this in a calculated column via DAX?
On a separate note, I can split the tables up into a City and Country table, but because there are a lot of duplicates in the City Table I'd like to avoid that if possible.
Solved! Go to Solution.
This is the kind of data shaping to be done with M (Power Query), not with DAX.
In "Edit queries", add column "Country" with formula
if [CountryOrCity] = "Country" then [Location] else null
Next, go to the Tranform tab, make sure the new Country column is selected, and choose Fill - Fill Down.
This is the kind of data shaping to be done with M (Power Query), not with DAX.
In "Edit queries", add column "Country" with formula
if [CountryOrCity] = "Country" then [Location] else null
Next, go to the Tranform tab, make sure the new Country column is selected, and choose Fill - Fill Down.
Thanks for the help! I originally derived the Country/City column as a calculated column so doing it via Power Query wasn't intuitive to me but your answer brought me back to the right track.
On a separate note, does that also mean that there is no good way of doing this via a calculated column rather than via M?
In my view, it is not a good way to do it in DAX.
Then again, it might still be possible in DAX, but that's not my expertise, so I just don't know and I leave the answer to that question to the DAX experts over here.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |