DAX Formula Help

Anonymous — Wed, 08 May 2019 19:34:06 GMT

I'm hoping you may be able to help. I'm creating a report for interns over years. There is a column telling which program an intern is in, SIE (Summer Intern experience) or SH (Summer Hire). The user wants to see if there is any SH converting into a SIE. I would like to see this trend of conversion over the years.

I have created a pivot table in excel and have this formula:

=IF(OR(GETPIVOTDATA("True/False",$A$3,"Intern",A6,"SIE/SH","SH")=0,GETPIVOTDATA("True/False",$A$3,"Intern",A6,"SIE/SH","SIE")=0),0,1)

I have created a matrix table in Power BI to display the same as the pivot table in excel, however I don't know how to write the DAX formala to give me the same results as the excel formula.

Any suggestions?

Re: DAX Formula Help

technolog — Sat, 03 Feb 2024 17:54:55 GMT

Hello,

To replicate the functionality of your Excel formula in Power BI using DAX, you'll need to create a measure that evaluates whether an intern has converted from a Summer Hire (SH) to a Summer Intern Experience (SIE) program. The Excel formula you are using checks if an intern has records in both SH and SIE categories and returns 1 if true, otherwise 0.

In Power BI, you can achieve this with a combination of CALCULATE and FILTER functions within a measure. Here is an example of how you might structure your DAX formula:

InternConversion =
VAR InternSH = CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[SIE/SH] = "SH"))
VAR InternSIE = CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[SIE/SH] = "SIE"))
RETURN IF(InternSH > 0 && InternSIE > 0, 1, 0)

topic DAX Formula Help in DAX Commands and Tips

DAX Formula Help

Re: DAX Formula Help