need help with writing DAX query

[Anonymous]

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

[v-piga-msft]

Hi @Anonymous ,

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

[Anonymous]

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

[v-piga-msft]

Hi @Anonymous ,

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

[Greg_Deckler]

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.