NVL equivalent in DAX but in 2 different tables

[missmanou26]

Hello all,

I have an issue with a DAX function, I want to make equivalent to this:

IF(D_IMMEUBLE[IMM_OPE]=BLANK(),RELATED(D_IMMEUBLE[IMM_OPE]),D_SOCIETE[SOC_OPE_DEF]))

I have one to many relationship between D_SOCIETE and D_IMMEUBLE.

But when I run the DAX formula I have this message issue:

A single value for column 'IMM_OPE' in table 'D_IMMEUBLE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 How can I fix this problem please ?

Many thanks for your help.

[missmanou26]

Sorry, I've just granted the permissions.

Thank you !

[v-yiruan-msft · Accepted solution]

Hi @missmanou26 ,

As checked the model in your report file, there is no direct relationship between the table D_SOCIETE and D_IMMEUBLE. So it would get the error when using RELATED ( D_SOCIETE[SOC_OPE_DEF] ) in the formula.... I updated your sample pbix file(see the attachment), please check if that is what you want. Please update the formula of calculated column [Operation_GL] as below:

Operation_GL = 
VAR _sksoc =
    CALCULATE (
        MAX ( 'TR_TIERS'[SK_SOC] ),
        FILTER ( 'TR_TIERS', 'TR_TIERS'[SK_IMM] = 'D_IMMEUBLE'[SK_IMM] )
    )
VAR _sod =
    CALCULATE (
        MAX ( D_SOCIETE[SOC_OPE_DEF] ),
        FILTER ( 'D_SOCIETE', 'D_SOCIETE'[SK_SOC] = _sksoc )
    )
RETURN
    IF ( ISBLANK ( D_IMMEUBLE[IMM_OPE] ), _sod, D_IMMEUBLE[IMM_OPE] )

Best Regards

[missmanou26]

Yes it worked ! Thank you very much for your help

[v-yiruan-msft]

Hi @missmanou26 ,

It seems that what you are trying to create is a measure, that's why you got that error message. You can get more details in the following blog...

DAX error messages in Power BI

You can create a calculated column as below in the table 'D_IMMEUBLE' to replace the current measure:

Column =
IF (
    D_IMMEUBLE[IMM_OPE] = BLANK (),
    RELATED ( D_SOCIETE[SOC_OPE_DEF] ),
    D_IMMEUBLE[IMM_OPE]
)

If the above one can't help you, could you please provide some raw data in the table 'D_IMMEUBLE' and 'D_SOCIETE' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[missmanou26]

Hello @v-yiruan-msft 

I already tried to create a calculated column but I get also an error. Here's a simplified pbix file.

https://drive.google.com/file/d/1zMRU1VXHPyMolfVGeqUQxQNevrmGHknO/view?usp=share_link

Thanks a lot for your help.

[v-yiruan-msft]

Hi @missmanou26 ,

It seems that I don't have the proper access to your shared file, could you please grant me the sufficient permission to it? Thank you.

How to upload PBI in Community

Best Regards