cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rajavarman Frequent Visitor
Frequent Visitor

need help with writing DAX query

Hi,

I need to write an equivalent DAX query for the below SQL query

 

if exists (select RoleName from ifs.FactCustomerRialtoRole where CustomerID='132324' and RoleName='IFS_SUPPORT_ADMIN' and Status='Enabled')
begin
select distinct
l.ProjectKey ID
,l.ProjectName Project
from ifs.DimProject l
join ifs.FactIssueManagement f on l.ProjectKey=f.ProjectKey
order by 2
end
else
begin
select distinct
l.ProjectKey ID
,l.ProjectName Project
from ifs.DimProject l
join ifs.FactIssueManagement f on l.ProjectKey=f.ProjectKey
where l.ProjectName in (select RoleName from ifs.FactCustomerRialtoRole where CustomerID='132324'and Status='Enabled')
order by 2
end

 

I tried using if(contains(),1,0) for if exists(), but the if condition in dax returns only single value.

Any help on how to write the above sql query in dax would be appreciated.

 

Thanks

4 REPLIES 4
Super User
Super User

Re: need help with writing DAX query

Do you want a DAX query or a Power Query query? If Power Query, you can just paste your SQL into the Advanced section of a SQL Connector.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: need help with writing DAX query

Hi @Rajavarman ,

 

If you could share the data sample and your desired output so that we could create the Dax forumla based on the logic of your SQL query more easily.

 

In addition, you could refer to this article From SQL to DAX: Projection.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Rajavarman Frequent Visitor
Frequent Visitor

Re: need help with writing DAX query

Hi @v-piga-msft ,

 

Dimproject table has columns such as projectkey, projectname, etc. It has lsit of all the available projects.

As IFS_SUPPORT_ADMIN, I should be able to see all the projects in dimproject table provided the projectkey is there in factissuemanagement table.

Factcustomerrialto table has columns such as customerid, rolename, status, etc.

Rolename consists of roles and projects associated with each customer. For a particular customerid, if status is enabled, the that customer has access to that role or project. if the status is disabled, then he does not have access. IFS_SUPPORT_ADMIN is the role we need to check. if customer has status as enabled for that role, then he can see all the projects from dimproject table. if status is disabled for that role, than the he can see only the projects from dimproject table which are present in factcustomerrialtorole table with status as enabled

 

Community Support Team
Community Support Team

Re: need help with writing DAX query

Hi @Rajavarman ,

 

Could you share the sample data as table format which could reproduce your scenario so that I could have a test on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.