cancel
Showing results for
Did you mean:

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

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.

1 ACCEPTED SOLUTION
Solution Supplier

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)RETURNResult`

Table Snapshot:

`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)RETURNResult`

Output Snapshot:

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 👍

3 REPLIES 3
Solution Supplier

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)RETURNResult`

Table Snapshot:

`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)RETURNResult`

Output Snapshot:

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 👍

Frequent Visitor

Thank you!!! 🙂 it works

Solution Supplier

@PatCreator You are welcome 😀👍

Announcements

New forum boards available in Real-Time Intelligence.

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

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors