Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
85 | |
73 | |
69 | |
64 |
User | Count |
---|---|
214 | |
124 | |
117 | |
82 | |
76 |