Reply
PatCreator
Frequent Visitor
Partially syndicated - Outbound

Table Repeat value based on other column

Hi All,

I want to create a calculation "Baseline" like the one below that contains the 1st value from [Pop Coverage] col and its repeated though the whole [Year] Col.

please help, I've spent so many hours 😞

PatCreator_1-1684266175046.png

 

 

1 ACCEPTED SOLUTION
ghoshabhijeet
Solution Supplier
Solution Supplier

Syndicated - Outbound

@PatCreator  Thanks for your query.

Here is my solution:

1. Create a calculate column for Date in the table using the below DAX:

Date = 
VAR MonthNum = SWITCH(
TRUE(),
'DataTable'[Month] = "January",1,
'DataTable'[Month] = "February",2,
'DataTable'[Month] = "March",3,
'DataTable'[Month] = "April",4,
'DataTable'[Month] = "May",5,
'DataTable'[Month] = "June",6,
'DataTable'[Month] = "July",7,
'DataTable'[Month] = "August",8,
'DataTable'[Month] = "September",9,
'DataTable'[Month] = "October",10,
'DataTable'[Month] = "November",11,
'DataTable'[Month] = "December",12
)
VAR Result = DATE( 'DataTable'[Year], MonthNum , 1)

RETURN
Result

 

Table Snapshot:

 

ghoshabhijeet_0-1684270987005.png

 

 

2. Create a measure for Baseline:
Baseline =
VAR MinDate =
CALCULATE (
MIN ( 'DataTable'[Date] ),
ALL ( 'DataTable' ),
'DataTable'[Year] = SELECTEDVALUE ( 'DataTable'[Year] )
)
VAR Result =
CALCULATE (
MIN ( 'DataTable'[Pop Coverage] ),
ALL ( 'DataTable' ),
'DataTable'[Year] = SELECTEDVALUE ( 'DataTable'[Year] ),
'DataTable'[Date] = MinDate
)
RETURN
Result


Output Snapshot:

ghoshabhijeet_1-1684271043226.png

Hope this helps.

 

 

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

View solution in original post

3 REPLIES 3
ghoshabhijeet
Solution Supplier
Solution Supplier

Syndicated - Outbound

@PatCreator  Thanks for your query.

Here is my solution:

1. Create a calculate column for Date in the table using the below DAX:

Date = 
VAR MonthNum = SWITCH(
TRUE(),
'DataTable'[Month] = "January",1,
'DataTable'[Month] = "February",2,
'DataTable'[Month] = "March",3,
'DataTable'[Month] = "April",4,
'DataTable'[Month] = "May",5,
'DataTable'[Month] = "June",6,
'DataTable'[Month] = "July",7,
'DataTable'[Month] = "August",8,
'DataTable'[Month] = "September",9,
'DataTable'[Month] = "October",10,
'DataTable'[Month] = "November",11,
'DataTable'[Month] = "December",12
)
VAR Result = DATE( 'DataTable'[Year], MonthNum , 1)

RETURN
Result

 

Table Snapshot:

 

ghoshabhijeet_0-1684270987005.png

 

 

2. Create a measure for Baseline:
Baseline =
VAR MinDate =
CALCULATE (
MIN ( 'DataTable'[Date] ),
ALL ( 'DataTable' ),
'DataTable'[Year] = SELECTEDVALUE ( 'DataTable'[Year] )
)
VAR Result =
CALCULATE (
MIN ( 'DataTable'[Pop Coverage] ),
ALL ( 'DataTable' ),
'DataTable'[Year] = SELECTEDVALUE ( 'DataTable'[Year] ),
'DataTable'[Date] = MinDate
)
RETURN
Result


Output Snapshot:

ghoshabhijeet_1-1684271043226.png

Hope this helps.

 

 

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

Syndicated - Outbound

Thank you!!! 🙂 it works

Syndicated - Outbound

@PatCreator You are welcome 😀👍

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)