Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 whe...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.