Excel Countifs Vs PowerBI Countrows

[Anonymous]

Hey guys,

I'm just wondering if you could help me find the bug on my DAX formula we're just replicating the ones in Excel. The data set is the same, since most replicated formulas are now giving the same results except for this one.

The formula in Excel is and giving me a sum or count of 13: 

=IF(Y180294=1,IF(COUNTIFS(G:G,G180294,E:E,">"&E180294,E:E,"<="&E180294+'VAContain NewM'!$E$2,Y:Y,0)>0,1,0),0)

While the one in PBI is giving me a sum of 26 on the same date reference: 

Called back = IF('Claims Epic - Interactions Report'[VoiceAICont_aftr]=1,

IF(CALCULATE(

COUNTROWS('Claims Epic - Interactions Report'),

    FILTER(

        'Claims Epic - Interactions Report',

        'Claims Epic - Interactions Report'[ANI]=EARLIER('Claims Epic - Interactions Report'[ANI]) &&

        'Claims Epic - Interactions Report'[Date]>EARLIER('Claims Epic - Interactions Report'[Date]) &&

        'Claims Epic - Interactions Report'[Date]<=EARLIER('Claims Epic - Interactions Report'[Date])+2 &&

        'Claims Epic - Interactions Report'[VoiceAICont_aftr]=0

    ))>0,1,0)

,0)

I hope somebody will able to help. Please note that most replicated formulas are matching excel except for this remaining 1 so we're sure that both data sources are the same. date column was used as well as reference in other formulas. thanks!

[Anonymous]

Hello,

Thank you for the response. We've prepared dummy data and dummy PBIX for your reference. Kindly refer to columns column9 and Column9_PBIequi for your analysis. Thank you. Please also refer to the ff link for the downloadable files: https://drive.google.com/drive/folders/1O0Bg0AOYXn8S827UGFfrgAAfOB8UqzaG 

[Anonymous]

Hi @Anonymous ,

It seems that I have no sufficient privilledge to access your file. Could you please grant me the proper access to it? Thank you.

Best Regards

[Anonymous]

Hi,

I just updated the permission. You may now try to download again. Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thanks for granting the permission to access the file, now I can access it successfully. As checked the file, please update the formula of calculated column [Column9_PBIequi] as below and you can get the expected result...

Column9_PBIequi = 
IF (
    'Dummy data'[Column8_PBIequi] = 1,
    IF (
        COUNTROWS (
            FILTER (
                'Dummy data',
                'Dummy data'[Column2] <> BLANK ()
                    && 'Dummy data'[Column2] = EARLIER ( 'Dummy data'[Column2] )
                    && 'Dummy data'[Date] > EARLIER ( 'Dummy data'[Date] )
                    && 'Dummy data'[Date]
                        <= EARLIER ( 'Dummy data'[Date] ) + 2
                    && 'Dummy data'[Column8_PBIequi] = 0
            )
        ) > 0,
        1,
        0
    ),
    0
)

Best Regards

[Anonymous]

Yes! its now matching! Thank you so much. I hope this helps others with same concern 🙂

[Anonymous]

Hi @Anonymous ,

If I understand correctly, you are trying to get the count of rows which fulfill some special conditions in Power BI. But the return result(26) in Power BI is different with the one(13) in Excel. And as checked the formula of your created calculated column [Called back] seems OK. In order to get a better understanding on your problem and give you a suitable solution shortly, could you please provide the following info? It would be helpful to find out the solution. Thank you.

1. 'Column Y in excel is corresond to 'Claims Epic - Interactions Report'[VoiceAICont_aftr]?
2. Column G in excel is correspond to 'Claims Epic - Interactions Report'[ANI] in Power BI? 
3. Column E in excel is correspond to 'Claims Epic - Interactions Report'[Date] in Power BI? 
4. The value of 'VAContain NewM'!$E$2 in excel is always equal to 2? 180294 is the line number for per column?
5. Could you please provide some sample data in these columns of excel? 

It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Kindly see my response above