Forum Discussion

franorio's avatar
franorio
Helper III
9 years ago
Solved

Calculated Formula to count

Hello everybody,  Today i have the following report on excel: On my Power BI already created different Tables: one with a calendar table, other with the 5 Countries, other with the 8 Areas. And...
Need Help
  • franorio's avatar
    franorio
    9 years ago

    Hi v-ljerr-msft!

    Sorry to bother you again. I created a secondary relationship (dotted line) between my DimArea Table and my Area column of MovementsReport. I need to use the following formula

     

    COUNT of sumar cf = CALCULATE(
    COUNTROWS('MovementsReport') ,
    FILTER(
    'MovementsReport',
    NOT ISBLANK('MovementsReport'[Sumar CF a])
    )
    )

     

    and add a USERELATIONSHIP(DimAreas[Área],MovementsReport[Area])

     

    to add this value to Area Column and not Previous Area (Active relationship).

     

    I don't know why, when i create this measure i'm getting an error, too many arguments for filter. Could you please help me to get the right formula.

     

    Regards!

     

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi franorio,

 

Could you try the formula below to see if it works in your scenario?

 

Mov Adherence =
VAR selectedTable =
    CALCULATETABLE ( 'MovementsReport' )
RETURN
    CALCULATE (
        COUNTROWS ( 'MovementsReport' ),
        FILTER ( 'MovementsReport', 'MovementsReport'[Status] = "OK" )
    )
        + COUNTROWS (
            FILTER (
                ALL ( 'MovementsReport' ),
                CONTAINS (
                    selectedTable,
                    'MovementsReport'[Sumar CF], 'MovementsReport'[Área Anterior]
                )
            )
        )

Total Mov =
VAR selectedTable =
    CALCULATETABLE ( 'MovementsReport' )
RETURN
    CALCULATE (
        COUNTROWS ( 'MovementsReport' ),
        FILTER (
            'MovementsReport',
            'MovementsReport'[Status] = "OK"
                || 'MovementsReport'[Status] = "NO OK"
        )
    )
        + COUNTROWS (
            FILTER (
                ALL ( 'MovementsReport' ),
                CONTAINS (
                    selectedTable,
                    'MovementsReport'[Sumar CF], 'MovementsReport'[Área Anterior]
                )
            )
        )

 

Regards

franorio's avatar
franorio
Helper III
9 years ago

Hi v-ljerr-msft!

Sorry to bother you again. I created a secondary relationship (dotted line) between my DimArea Table and my Area column of MovementsReport. I need to use the following formula

 

COUNT of sumar cf = CALCULATE(
COUNTROWS('MovementsReport') ,
FILTER(
'MovementsReport',
NOT ISBLANK('MovementsReport'[Sumar CF a])
)
)

 

and add a USERELATIONSHIP(DimAreas[Área],MovementsReport[Area])

 

to add this value to Area Column and not Previous Area (Active relationship).

 

I don't know why, when i create this measure i'm getting an error, too many arguments for filter. Could you please help me to get the right formula.

 

Regards!