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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to return a value from row context of matrix with a blank value (SELECTEDVALUE)

I am attempting to lookup a value from one table to another. I have tried many different methods of relating the data including a mapping table of distinct values, many-many relationships, etc.

 

I have two tables, Actuals and Plan. I want to relate them by account # and cost center #. The core issue is that there are some instances where there have been NO actuals on an account for a cost center but there is a PLAN value on that account/cost center. The PLAN # is the same for each cost center for every month.


I want to use this measure to look up the value from the plan table, but the selected value fails when there is no value in the row, see screenshot below.

GoatCorp_0-1661302285228.png

 

As you can see for Month 2 the measure happily returns the PLAN value because selected value is satisfied with the row context having a value. However January plan is not showing up even though the plan # on that cost center is the exact same, I believe it because selected value isn't actually grabbing anything.

 

Plan Measure = calculate(sum('Plan Data Tabular'[Amount $]), 'Plan Data Tabular'[Coa Code for Mapping] = SELECTEDVALUE('ACDOCA_GL Actuals'[GL Account]),'Plan Data Tabular'[Cost Center Cd Adjusted for Plan Blanks] = SELECTEDVALUE('ACDOCA_GL Actuals'[Cost Center]))
 
I apologize if this explanation is convoluted but I think the screenshot is quite clear, how do I use DAX to write a measure that forces a lookup by the cost center value on the left, even if there is no value for that month in the matrix.
 
2 REPLIES 2
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@Anonymous , if there is no data in 'ACDOCA_GL Actuals'[GL Account] for Jan for that CostCentre, then the expression SELECTEDVALUE('ACDOCA_GL Actuals'[GL Account]) will return BLANK. So the overall CALCULATE expression will return BLANK.

I would guess that the field that you are using in the matrix for GLAccount is 'ACDOCA_GL Actuals'[GL Account].

 

To solve this, I would suggest creating and using a dimension table for GLAcount, and using the dimension field in the matrix. That way there will be a value for GLAccount for Jan so that the expression SELECTEDVALUE('dimAccounts'[GL Account]) will return a value, which will allow the CALCULATE expression to retrieve the correct value for [Plan Measure] for that account for Jan.

You will need to change [Plan Measure] to

Plan Measure = 
    calculate(
        sum('Plan Data Tabular'[Amount $]),
        'Plan Data Tabular'[Coa Code for Mapping] = SELECTEDVALUE('dimAccount'[GL Account]),
        'Plan Data Tabular'[Cost Center Cd Adjusted for Plan Blanks] = SELECTEDVALUE('dimAccount'[Cost Center])
    )

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.