Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need help on convert Sql query to DaX

Hello All,

I have a sample data of sales which has applications, their categories and sales.

I need to calculate the distinct count of applications which has sales as zero or blank.



I have Sample data like the above picture, I need to calculate the count of applications
I have written the Sql Query But need help in converting it into DAX
 
select Application,Count(Application)as appcount, SUM(ISNULL([Actual Date],0)) from DWH.[Report Check Data]
Where IsDeleted=0
Group By Application having SUM(ISNULL([Actual Date],0))=0
Order By Application

when I tried the above query in sql i am getting the below output

 
Can anyone please help me on this..

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    According to your statement, I think your issue should be caused by that OR() function will return True or False result, you need to add it in FILTER() function.

    _Modified Count of Actual date <0 =
CALCULATE (
    DISTINCTCOUNT ( 'Monitoring Report Check Data'[Application] ),
    FILTER (
        'Monitoring Report Check Data',
        OR ( ISBLANK ( [_SUM of actual date] ), [_SUM of actual date] = 0 )
    )
)

     

    Best Regards,
    Rico Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    audreygerred 
    Thanks for your response, I have tried your solution but giving the below error.

    I am expecting the result of Count of Application column from below sql query output which should be 3.
    select Application,Count(Application)as appcount, SUM(ISNULL([Actual Date],0)) from DWH.[Report Check Data]
    Where IsDeleted=0
    Group By Application having SUM(ISNULL([Actual Date],0))=0
    Order By Application

    Please help..

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous ,

       

      According to your statement, I think your issue should be caused by that OR() function will return True or False result, you need to add it in FILTER() function.

      _Modified Count of Actual date <0 =
CALCULATE (
    DISTINCTCOUNT ( 'Monitoring Report Check Data'[Application] ),
    FILTER (
        'Monitoring Report Check Data',
        OR ( ISBLANK ( [_SUM of actual date] ), [_SUM of actual date] = 0 )
    )
)

       

      Best Regards,
      Rico Zhou

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.