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

[Anonymous]

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.

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.

[v-xiaotang]

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]

@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])
    )