Please help me for creating DAX compare to a SQL

[Anonymous]

Hi,

Need to create DAX based on below SQl:

select SUM(EMDM_Support.ms.INVALID_RECORDS) from
EMDM_Support.EDG_COUNT_STATUS ms inner join EMDM_Support.TGT_MAT_75 ch
on ms.EDG_ID = ch.EDG_ID
where
ms.PROFILE_ID=75 and ch.status='Article Number has Invalid Length';

Please provide me the DAX related to above SQL.

[amitchandak]

Bring table EMDM_Support.ms. and EMDM_Support.EDG_COUNT_STATUS to power bi and join then on EDG_ID.
I am assuming it would be 1 to many or many to 1

calculate(sum('EMDM_Support.EDG_COUNT_STATUS'[INVALID_RECORDS]),'EMDM_Support.EDG_COUNT_STATUS'[PROFILE_ID] =75,'EMDM_Support.ms.INVALID_RECORDS'[status]="Article Number has Invalid Length")

Correct table name as per need.

[Anonymous]

Hi,

actually two tables are related with EDG_ID and I have made a relation between them with that ID and it is Many to Many relation.

Now there is no data available for 'TGT_MAT_75'[status]="Article Number has Invalid Length" in TGT_MAT_75 table. So what would be the result is NULL value as because there is no status for this in TGT_MAT_75 table.

I have used the below expression below alreday:

Measure 3 = calculate(sum('EDG_COUNT_STATUS'[INVALID_RECORDS]),

'EDG_COUNT_STATUS'[PROFILE_ID] =75,

'TGT_MAT_75'[status]="Article Number has Invalid Length"

)

and I am getting wrong result and it is 87696655 , but it should be NUll or blank() value.

Please help me on this.

[amitchandak]

@Anonymous 

First check measure 4, if it does not give 0, then the problem is there with the filter. if it gives 0 then check for  measure 3

measure 4=  calculate(countrows('TGT_MAT_75'),
'TGT_MAT_75'[status]="Article Number has Invalid Length"
)

Measure 3 = calculate(sum('EDG_COUNT_STATUS'[INVALID_RECORDS]),
'EDG_COUNT_STATUS'[PROFILE_ID] =75,
filter('TGT_MAT_75','TGT_MAT_75'[status]="Article Number has Invalid Length")
)

[Anonymous · Accepted solution]

Hi,

I am taking aplology  for that as because I have just discovered that my previous relation ship was not correct, in Many to Many relation ship there I just chnaged the filter condition to:  Single TGT_MAT_75 Filters EDG_COUNT_STATUS and it just solved the problem using the below DAX:

Measure 3 = calculate(sum('EDG_COUNT_STATUS'[INVALID_RECORDS]),
'EDG_COUNT_STATUS'[PROFILE_ID] =75,
'TGT_MAT_75'[status]="Article Number has Invalid Length"
)

But thanks to you for being with me.

[amitchandak]

@Anonymous Does the above reply helps. if you need more help make me @

Appreciate your Kudos.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

These usually do not go very well when you just paste SQL and want the DAX equivalent. I suggest you post sample data, expected output and you explain in plain language what you are trying to do with your SQL.

DAX does have a NATURALINNERJOIN function.