Forum Discussion
DAX Formula Help
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?
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)
1 Reply
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)