Excel DAX to Power BI

[Anonymous]

Hello and Happy Friday!

I am trying to recreate a Excel report in Power BI and I am almost there but I am having trouble with 2 calculations and how to convert the syntax that is in Excel over to Power BI DAX.

The Excel report uses a COUNTIF vs something like - 

TMTM1 =

CALCULATE (

COUNTA( 'All Data'[TechNameCompare1] ),ALLEXCEPT('All Data', 'All Data'[TechMatch])

)

Suggestions?

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try to use FIND function to get the two columns.For example:

Column =
CALCULATE (
    COUNTROWS ( 'Sample' ),
    FILTER (
        ALLEXCEPT ( 'Sample', 'Sample'[TechName] ),
        FIND ( "Exists in both GoCanvas and e-automate", 'Sample'[TechMatch], 1, 0 ) > 0
            && FIND ( "MAC", 'Sample'[MAC?], 1, 0 ) > 0
            && FIND ( "Exist", 'Sample'[MAC?], 1, 0 ) > 0
    )
)

Column 2 =
CALCULATE (
    COUNTROWS ( 'Sample' ),
    FILTER (
        ALLEXCEPT ( 'Sample', 'Sample'[TechName] ),
        FIND ( "Exists in both GoCanvas and e-automate", 'Sample'[TechMatch], 1, 0 ) > 0
            || FIND ( "e-automate Only,not in GoCanvas", 'Sample'[TechMatch], 1, 0 ) > 0
    )
)

Regards,

[Anonymous]

Here is what I ended up with that seems to get me what I needed.

Thank you so much!

TMTM1 = IF(CALCULATE ( COUNTROWS ( 'All Data' ),

        FILTER ( ALLEXCEPT ( 'All Data', 'All Data'[TechName] ),

            FIND ( "Exists in both GoCanvas and e-automate", 'All Data'[TechMatch], 1, 0 ) > 0 ||

            FIND ( "MAC Exists", 'All Data'[MacMatch], 1, 0 ) > 0 ) ) > 0,

                CALCULATE ( COUNTROWS ( 'All Data' ),

                    FILTER ( ALLEXCEPT ( 'All Data', 'All Data'[TechName] ),

                        FIND ( "Exists in both GoCanvas and e-automate", 'All Data'[TechMatch], 1, 0 ) > 0 ||

                        FIND ( "MAC Exists", 'All Data'[MacMatch], 1, 0 ) > 0 ) ), 0)

TMTM4 = IF(CALCULATE ( COUNTROWS ( 'All Data' ),

    FILTER ( ALLEXCEPT ( 'All Data', 'All Data'[TechName] ),

        FIND ( "Exists in both GoCanvas and e-automate", 'All Data'[TechMatch], 1, 0 ) > 0 ||

FIND ( "e-automate Only, not in GoCanvas", 'All Data'[TechMatch], 1, 0 ) > 0 ) ) > 0 ,

            CALCULATE ( COUNTROWS ( 'All Data' ),

                FILTER ( ALLEXCEPT ( 'All Data', 'All Data'[TechName] ),

                    FIND ( "Exists in both GoCanvas and e-automate", 'All Data'[TechMatch], 1, 0 ) > 0 ||

                    FIND ( "e-automate Only, not in GoCanvas", 'All Data'[TechMatch], 1, 0 ) > 0 ) ),0)

[Anonymous]

AWESOME!!

One last question, where would I add an ELSE to return 0 so that the field is populated with a value?

Thank you SO MUCH!!