Forum Discussion
Please help me for creating DAX compare to a SQL
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.
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.
6 Replies
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.
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 1calculate(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 Does the above reply helps. if you need more help make me @
Appreciate your Kudos.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.
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") )
