Convert Excel formula to DAX

[Joakim_gg]

Hi,

As I'm fairly new to Power BI I have a formula in Excel which I would like some input on how to use in Power BI. 

The formula is as follows: 

=IF(COUNT(I7:Q7)>0;(((IF(I7="";0;I7))+(IF(J7="";0;J7))+(IF(L7="";0;L7))+(IF(N7="";0;N7))+(IF(O7="";0;O7))+(IF(K7="";0;(5-K7)))+(IF(M7="";0;(5-M7)))+(IF(P7="";0;(5-P7)))+(IF(Q7="";0;(5-Q7))))/COUNT(I7:Q7));"")

Any help is much appreciated. Thanks!

[v-yingjl · Accepted solution]

Hi @Joakim_gg ,

If you want to achieve it using DAX in Power BI, you need to create a reference unpviot table like this in Power Query first:

Create a calculated column like this:

Result = 
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Reference table' ),
        FILTER (
            'Reference table',
            'Reference table'[Category] = EARLIER ( 'Table'[Category] )
        )
    )
VAR A =
    IF ( [Column1] = BLANK (), 0, [Column1] )
VAR B =
    IF ( [Column2] = BLANK (), 0, [Column2] )
VAR C =
    IF ( [Column4] = BLANK (), 0, [Column4] )
VAR D =
    IF ( [Column6] = BLANK (), 0, [Column6] )
VAR E =
    IF ( [Column7] = BLANK (), 0, [Column7] )
VAR F =
    IF ( [Column3] = BLANK (), 0, 5 - [Column3] )
VAR G =
    IF ( [Column5] = BLANK (), 0, 5 - [Column5] )
VAR H =
    IF ( [Column8] = BLANK (), 0, 5 - [Column8] )
VAR I =
    IF ( [Column9] = BLANK (), 0, 5 - [Column9] )
VAR result =
    IF ( _count > 0, ( A + B + C + D + E + F + G + H + I ) / _count, BLANK () )
RETURN
    result

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

Hello, welcome to the forum.

Can you repost your question please?

Please provide a small amount of sample data and the desired output (with an explanation of how to get there)