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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PatCreator
Frequent Visitor

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

@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

@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 👍

 

Thank you!!! 🙂 it works

@PatCreator You are welcome 😀👍

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.