Convert SQL to DAX
Hi, I have a paginated report which access the source as Power BI data model, both are deployed in service. I have set of parameters exist in Paginated Report. For ex. ProgramName. It contains list of programnames and use will select the respective program for seeing the data. Now, we have RLS implemented in data model and it is working fine. However, the user can see all the programs in parameter rather he want see only his program. I am planning to restrict the program names in parameter based on user logged in to the system. I have tried below but no luck. SQL: select distinct P.ProgramId ,P.ProgramName ,DU.InternalEmailAddress from gold.programid P INNER Join gold.dimclinic C ON P.ProgramId = C.ProgramId INNER Join gold.userclinicbridge UC ON C.ClinicId = UC.ClinicId INNER JOIN gold.dimuser DU ON DU.UserId = UC.UserId WHERE DU.InternalEmailAddress = @UserId DAX: EVALUATE ADDCOLUMNS( 'Program'[ProgramId], 'Program'[ProgramName], LOOKUPVALUE('dimclinic'[ProgramId],'Program'[ProgramId]), LOOKUPVALUE('userclinicbridge'[ClinicId],'dimclinic'[ClinicId]), "InternalEmailAddress",LOOKUPVALUE('dimuser'[InternalEmailAddress],'dimuser'[UserId],'userclinicbridge'[ClinicId]) ) I need to apply filter as I mentioned in SQL. As I am new to DAX, seeking some help. ThanksHow to convert SQL to DAX
Hi Everyone, Can someone help me convert this SQL query to DAX for creating new measure. I tried several ways but could not figure it out if someone could help out I would really appreciate it. Thanks in Advance. WITH SANAL AS ( SELECT DISTINCT "COLUMN A","COLUMN B", "COLUMN C", CASE WHEN TRIM("MONTH") = '-1' THEN 0 WHEN TRIM("MONTH") = 'OCAK' THEN 1 WHEN TRIM("MONTH") = 'ŞUBAT' THEN 2 WHEN TRIM("MONTH") = 'MART' THEN 3 WHEN TRIM("MONTH") = 'NISAN' THEN 4 WHEN TRIM("MONTH") = 'MAYIS' THEN 5 WHEN TRIM("MONTH") = 'HAZIRAN' THEN 6 WHEN TRIM("MONTH") = 'TEMMUZ' THEN 7 WHEN TRIM("MONTH") = 'AĞUSTOS' THEN 8 WHEN TRIM("MONTH") = 'EYLÜL' THEN 9 WHEN TRIM("MONTH") = 'EKIM' THEN 10 WHEN TRIM("MONTH") = 'KASIM' THEN 11 WHEN TRIM("MONTH") = 'ARALIK' THEN 12 END as "MONTH", CASE WHEN "COLUMN D" = -1 THEN 0 WHEN "COLUMN D" = 2022 THEN 1 WHEN "COLUMN D" = 2023 THEN 3 ELSE "COLUMN D" END as "COLUMN D" FROM power_bi_test ),KUMULE_FILTRELEME AS( SELECT DISTINCT Row_number() OVER(PARTITION BY "COLUMN A" ORDER BY "COLUMN A","COLUMN D" DESC,"MONTH" DESC), "COLUMN A", "COLUMN D","MONTH", "COLUMN B","COLUMN C" FROM SANAL AA ),COLUMN C AS( SELECT DISTINCT Row_number() OVER(PARTITION BY "COLUMN C","COLUMN A" ORDER BY "COLUMN A" DESC,"COLUMN D" DESC,"MONTH" DESC),"COLUMN A", "COLUMN B","COLUMN C","COLUMN D","MONTH" FROM SANAL AA WHERE "COLUMN C" IN( SELECT "COLUMN C" FROM SANAL WHERE "COLUMN B" = 'Boş' ) AND "COLUMN B" != 'Boş' ),COLUMN C_FILTRELEME AS( SELECT * FROM COLUMN C BB WHERE row_number=1 ), SANAL1 AS ( SELECT "COLUMN A", CASE WHEN "COLUMN B" = 'Boş' THEN ( SELECT MAX("COLUMN B") FROM COLUMN C_FILTRELEME BB WHERE BB."COLUMN C" = AA."COLUMN C" AND BB."COLUMN B" != 'Boş' ) ELSE "COLUMN B" END AS "COLUMN B" FROM KUMULE_FILTRELEME AA WHERE row_number = 1 GROUP BY "COLUMN A", "COLUMN B", "COLUMN C" ) SELECT "COLUMN A", "COLUMN B" FROM SANAL1
How to mimic a query's constraints in DAX
Hi all, I am connected to a live database in PBI via a Dataflow. I use a simple query to give me a count of what I am looking for and need to replicate that in DAX. Query: SELECT count(IPID) as theSwitches FROM KUB.MVIEW_E_SWITCH WHERE (NORMALPOSITIONA = 0 OR NORMALPOSITIONB = 0 OR NORMALPOSITIONC = 0) AND (FEEDERID <> FEEDERID2) AND FEEDERID2 is not null Based on my DAX statement now, I just have it flagged TRUE or FALSE and am just counting the TRUE values in my report section, but it doesn't have to be formatted this way. I cannot get the number of true values to replicate the values from the query when I run it in SQL Developer. Here is my current DAX: TIE= (MVIEW_E_SWITCH[NORMALPOSITIONA]= 0 || MVIEW_E_SWITCH[NORMALPOSITIONB] = 0 || MVIEW_E_SWITCH[NORMALPOSITIONC]= 0) && (MVIEW_E_SWITCH[FEEDERID] <> MVIEW_E_SWITCH[FEEDERID2]) && NOT(ISBLANK(MVIEW_E_SWITCH[FEEDERID2])) Can anyone help me understand why the count of the two are different?SQL to DAX Conversion
How to convert below SQL query to DAX- select distinct fo.outlet_Id , o.[Outlet_Cd] as outletcode from [SDDW].[FCT_USER_EV_DTLS] fo (nolock) inner join (select distinct F.outlet_Id , F.EV_dt from [SDDW].[FCT_USER_EV_DTLS] f (nolock) where f.ev_dt <='2020-10-31' and f.ctry_id = 6 and f.ev_nm = 'app_remove' and f.busn_catg_cd='HPC' ) apprem on fo.outlet_Id = apprem.outlet_Id and fo.EV_dt > apprem.EV_dt inner join [SDDW].[DIM_OUTLET] o on fo.outlet_id = o.outlet_id where fo.ev_dt <='2020-10-31' and fo.busn_catg_cd='HPC' and fo.ctry_id = 6 and fo.ev_nm not in ('app_remove','')
Identify NEW clients to a service - from SQL to DAX
Trying to identify clients who have not engaged with a service before (but may have already engaged with another service so are already in the database). Have previously managed this with SQL but not sure how to convert to DAX. The SQL looks lit this: set @StartDate = '20190401' set @EndDate = '20200331' set service = 'SWRK' select * from ( --Total select a.actiontypeid Service, 'Total Clients Seen' ColumnName, count(distinct a.personid) as [Count],'' as [ ] from resadm.action A inner join resadm.Person P on P.PersonID = A.PersonID where a.dateofaction >= @StartDate and a.dateofaction <= @EndDate --and a.status='A' --and P.RecClassID = 'CCM' and a.actiontypeid in ('AODAD','AODYTH','WWO','HDX','CMH','TH1') and a.actiontypeid = service group by a.actiontypeid union --Total New select a.actiontypeid Service, 'Total New Clients' ColumnName, count(distinct a.personid) as [Count],'' as [ ] from resadm.action A inner join resadm.Person P on P.PersonID = A.PersonID left join resadm.action A2 on A2.PersonID = A.PersonID and A2.DateOfAction < @StartDate where a.dateofaction >= @StartDate and a.dateofaction <= @EndDate --and a.status='A' --and P.RecClassID = 'CCM' and A2.ActionID is null and a.actiontypeid in ('AODAD','AODYTH','WWO','HDX','CMH','TH1') and a.actiontypeid = service group by a.actiontypeidConvert SQL to Dax (Count Distinct using Join)
I want perform follwing sql query operation in Powerbi using Measure. Also i want to filter result of these query based on date using date slicer Query 1 : select distinct( DocNo) from InvoiceHistory where StatusDescription IN ('3-WAY MATCH ERROR') and statusid = '9e' and ManualProcessingFlag <> '1' and DocNo Not in (select distinct(DocNo) from InvoiceHistory where StatusDescription IN ('NEW','SUCCESSFULLY PROCESSED') )) I have tried following measure to achieve similar result. Measure = CALCULATE( DISTINCTCOUNT(InvoiceHistory[DocNo]), FILTER((InvoiceHistory), [StatusDescription]="3-WAY MATCH ERROR" && InvoiceHistory[StatusID]="9e" && InvoiceHistory[UpdatedDate] <= max(InvoiceHistory[DocDate]) &&InvoiceHistory[ManualProcessingFlag]<>1 && NOT( (Invoice[DocNo] in ( CALCULATETABLE (DISTINCT(Invoice[DocNo]), FILTER((Invoice),(invoice[StatusDescription]="New" || Invoice [StatusDescription] ="SUCCESSFULLY PROCESSED") ))))))) Please help.