Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sandip
Helper III
Helper III

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.

 

 

 

1 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.

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

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.

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.

 

 

 

@sandip 

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")
)

 

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.

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

Appreciate your Kudos.

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.