topic Re: ADD NEW COLUMN with DAX throwing error in DAX Commands and Tips

ADD NEW COLUMN with DAX throwing error

Help_Please — Wed, 13 Mar 2024 14:17:00 GMT

I have created a PBIX File on desktop with multiple tables. Out of those I have created an active relationship between 2 tables, Table A & Table B. Relationship between A&B is Many to One (Both) cardinality

Now I added a NEW COLUMN in Table A and entered a DAX Formula which comprises of multiple conditions (refer formula below) using Columns from both Tables A&B. The same formula worked in another PBIX File with a different data set.

However, despite both tables having an active relationship, the DAX formula is not suggesting Table B or any of it's Columns, so despite my formula being correct the result is an error with incomprehensible errors. Please help.

Please Note-

-Relationship Active

-All Columns in Text Format

-No data related errors upon refresh

-Tables refreshed both in Transform, front end as well in the Relationship Model

-Checked for duplications, blanks, etc. No data discrepancies

-DAX Formula is

New Column = MAXX(FILTER(
'TABLE A','TABLE A'[Date]>='TABLE B'[Migration Date]&&
'TABLE A'[Date]<='TABLE B'[Exit Date]&&
'TABLE A'[Unique Code]='TABLE B'[Unique Code]&&
'TABLE A'[Cluster Code]='TABLE B'[Cluster Code]),
'TABLE B'[Name])

Re: ADD NEW COLUMN with DAX throwing error

govindarajan_d — Wed, 13 Mar 2024 17:19:04 GMT

Hi Help_Please,

Can you try like this:

New Column = MAXX(FILTER( 'TABLE A','TABLE A'[Date]>=RELATED('TABLE B'[Migration Date])&& 'TABLE A'[Date]<=RELATED('TABLE B'[Exit Date])&& 'TABLE A'[Unique Code]=RELATED('TABLE B'[Unique Code])&& 'TABLE A'[Cluster Code]=RELATED('TABLE B'[Cluster Code])), 'TABLE B'[Name])

Re: ADD NEW COLUMN with DAX throwing error

Help_Please — Wed, 13 Mar 2024 20:50:29 GMT

Thank you for your response.

I did actually try the formula mentioned by you but unfortunately it is not giving the correct return values so although it doesn't throw an error but it is also not able to give me accurate results. I was wondering if there is a bug and if there was a way to diagnose that in my PBIX File.

Re: ADD NEW COLUMN with DAX throwing error

govindarajan_d — Thu, 14 Mar 2024 00:43:58 GMT

Hi Help_Please,

You are trying to run the MAXX function on name. Any specific reason?

Can you provide the pbix file with sensitive data removed?

Re: ADD NEW COLUMN with DAX throwing error

Help_Please — Thu, 14 Mar 2024 07:50:34 GMT

MAXX function I have used as an aggregator function so that it can return the exact value basis the conditions set. Somehow this formula works in another PBIX file but this file it is not able to return value. It's because it is not able to read the Table B and it's columns. I feel if I can resolve that the formula would also start working.

About sharing PBIX File, unfortunately, my company policies do not permit us to share any information including files with dummy data with outside company 😞