DAX Formula Help

[Anonymous]

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?

[technolog · Accepted solution]

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)