Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |