Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

SQL to Dax

SELECT ROUND(CAST(SUM(ctScanAbort) as float) /747683, 3)
  FROM [PetData].[dbo].[MachineKPI] 
  WHERE fk_machine IN (Select m.id from [PetData].[dbo].Machine m 
  INNER JOIN Country c ON m.fk_country = c.id WHERE m.active = 1 and c.reporting = 1 and fk_platform = 4) AND ctScanAbort IS NOT NULL;

Can someone help me out in converting this sql query to Dax, 

I tried this out but it shows error :

VAR FilteredMachineIDs =
CALCULATETABLE (
VALUES ( 'Machine'[id] ),
'Machine'[active] = 1,
'Machine'[fk_platform] = 4,
'Country'[reporting] = 1,
'Machine'[fk_country] = 'Country'[id]
)
VAR TotalCtScanAbort =
CALCULATE (
SUM ( 'FSMachineKPI'[ctScanAbort] ),
'FSMachineKPI'[ctScanAbort] IS NOT BLANK (),
'FSMachineKPI'[fk_machine] IN VALUES ( FilteredMachineIDs )
)
VAR Result =
ROUND ( DIVIDE ( TotalCtScanAbort, 747683.0 ), 3 )
RETURN
Result

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Tried out ChatGpt but it still dosent work

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi Anonymous in part TotalCtScanAbort part 'FSMachineKPI'[fk_machine] IN VALUES ( FilteredMachineIDs )

    try to replace with FilteredMachineIDs and see results