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

[carloP24]

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)...

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

Thank you very much for any help!

[carloP24]

Anyone that could help me with the topic?

Thank you!

[dufoq3]

You posted DAX question to Power Query forum...

[carloP24]

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]

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

Or you can do it using lists

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

use drilldown

and you get your value in a list

then you can use it

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

[carloP24]

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).

[serpiva64]

as you can see here

#"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)

[carloP24]

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!