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
carloP24
Frequent Visitor

Power Pivot reference to a single fixed value present in a table

Hi everyone,

 

I have created a measure in Power Pivot where I need to reference to a single value (fixed value) present in a table in the data model: the value is a date and I use it for a YEARFRAC calculation within the measure.

 

In practice, I would need to reference to the value present in the first row - second column of the below table (that is 31/12/2023)...

 

carloP24_2-1705158127624.png

 

...within the below measure, instead of the "30/09/2023" value inserted manually.

 

carloP24_0-1705233852720.png

 

 

Thank you very much for any help!

7 REPLIES 7
carloP24
Frequent Visitor

Anyone that could help me with the topic?

 

Thank you!

You posted DAX question to Power Query forum...


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

I'm aware of it, but I wasn't sure about the exact location of the post given the topic (the data comes from power query tables...). At this point I will also put it in "DAX Commands and Tips" (provided that is the correct forum...). Thank you

serpiva64
Super User
Super User

Hi,

you can reference one value in Power query.

You can directly extract it this way

= #"Changed Type"[Column2]{0} (first value of column2)

and then use it in a column of your second table

serpiva64_7-1705163708251.png

 

serpiva64_6-1705163576998.png

 

 

Or you can do it using lists

You need first to extract your value to a list with few steps

serpiva64_0-1705162957211.png

serpiva64_1-1705162992250.png

 

serpiva64_2-1705163040522.png

 

use drilldown

serpiva64_4-1705163205173.png

and you get your value in a list

serpiva64_3-1705163177363.png

then you can use it

serpiva64_5-1705163268563.png

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

 

 

 

 

Thank you very much for your help!

 

Your suggestion was helpful though, but actually what I really need is reference to a value in a query table (the date) within a measure in Power Pivot instead of use "30/09/2023" putted manually (as you can see the "Date" table is present in the data model so that I can use it in Power Pivot).

as you can see here

serpiva64_1-1705251660868.png

 

#"Solution 2 step 1" is not an hardcoded value but the value the value present in the first row - second column of the below table (that is 31/12/2023)

 

Hi,

 

Mmmm, ok, but I would like to know how to reference to that value within my Power Pivot measure (in particular in the YEARFRAC calculation of the measure) and, preferably, how to do it using the original "Date" query table.

 

Thank you!

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.

Top Solution Authors
Top Kudoed Authors