Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ongzph
Frequent Visitor

Help: Using earlier row values as condition

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:

Capture.PNG

 

 

 

I'd like to add a column that references the country each City/Country belongs to, as such:

Capture2.PNG

 

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.

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

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.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.