Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Excel Countifs Vs PowerBI Countrows

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!

 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Kindly see my response above

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi,

         

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