Want to use dimension in a calculation without changing it

[Paul2336]

I have 2 simple queries, one of which has a calculated measure 3 Month ANPR, the other which has a numerical column "Days in Period", which is being treated as a dimension, even though all values are numeric.  I want to calculate 3 Month ANPR / Days in Period, by Month End Date.  But since Days in Period is a dim, I have to convert it to a measure, which means an aggregate function, which changes the value.  I just want to use the column as a measure.  What can I do?  I've googled the #@%! out of this and can't find anything which doesn't change the values of Days in Period (e.g. Max, Average, etc.)  The queries are linked by Month End Date.  TIA for any help.

[rohit1991 · Accepted solution]

Hi @Paul2336 ,

You're encountering a common modeling issue in Power BI where a numeric column like "Days in Period" is treated as a dimension, making it tricky to use in calculations without applying an aggregate function that might distort the value. Since your data model has a one-to-one relationship between "Month End Date" in Query1 and Query2, and "Days in Period" has a single value per period, the best way to use it in your measure without changing the value is by using the SELECTEDVALUE() function. This function returns the exact value when only one is present in the current context—perfect for your use case—and avoids unnecessary aggregation like MAX or AVERAGE. You can then divide your [3 Month ANPR] measure by this selected value using the DIVIDE() function to handle divide-by-zero scenarios gracefully.

Here’s the DAX code to implement:

ANPR per Day = 
DIVIDE(
    [3 Month ANPR],
    SELECTEDVALUE(Query2[Days in Period])
)

[Paul2336]

Thanks - this worked perfectly.  Much appreciated!

[v-mdharahman]

Hi @Paul2336,

Thanks for reaching out to the Microsoft fabric community forum.

It sounds like the issue you're running into is due to Power BI treating your "Days in Period" column as a dimension, which is why it's forcing you to aggregate it in a measure. That’s expected behavior in Power BI as it requires an aggregation when using a column from a different table in a measure, even if the values are numeric.

If “Days in Period” only has one value per Month End Date (which it sounds like it does), you can safely use "SELECTEDVALUE()" in your measure to grab that single value without doing a traditional aggregation like MAX or AVERAGE that could skew results.

Something like this should work:

Divided ANPR =
DIVIDE(
[3 Month ANPR],
SELECTEDVALUE(Query2[Days in Period])
)

This will return the "Days in Period" value for the current Month End Date context as long as there's only one value per month.

That said, a better long-term approach would be to create a proper Calendar table and relate both of your queries to it using the Month End Date. You can add a calculated column to that Calendar with the number of days in the month like "Days in Period = DAY(EOMONTH([Date], 0))". This way, you’re centralizing your time logic and you won’t have to worry about inconsistent filtering or aggregations across multiple queries.

I would also take a moment to thank @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Paul2336,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @Paul2336,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[lbendlin]

But since Days in Period is a dim, I have to convert it to a measure

Not really?

Consider adding a proper Calendar table to your data model. Then "days in period" will be automatic.