Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Excel and Power BI Differ in Distinct Count Results

I have 686 rows in a table and only 286 of them are distinct. I've been able to correctly calculate the count of the total distinct values using a measure. I take the same calculation that I used in the distinct values measure and put it into a new measure (I'm not referencing the measure, I'm taking the actual formula) and then filter that for a specific value (let's call it FilterValue A), instead of returning 164 I'm getting 172. Furthermore, when I repeat that for FilterValue B, I get 108 rows when it should be 116.

I've included two variations of the measure involving FilterValueA and they both return the same results. Using a COUNTIF in Excel gives me the proper results (164 and 116 for FilterValueA and FilterValueB, respectively), but in DAX in Power BI I'm getting 172 and 108, respectively.

Am I doing something wrong here?

CALCULATE ( 
    DISTINCTCOUNT ( SC[Number]
    ),
    'Dim Approval Status'[Approval Status] = "A" 
)
CALCULATE ( 
    DISTINCTCOUNT ( SC[Number]
    ),
    FILTER ( SC, 
        RELATED ( 'Dim Approval Status'[Approval Status] ) = "A" 
    )
)​
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Measure will inact with other slicers, table visual, bar visual in the same page or other columns in the same table visual with this measure. You can use removefilters() to remove the column you do not want to filter by it.

     

    You can try some code like the following:

    CALCULATE ( 
    DISTINCTCOUNT ( SC[Number]),
    'Dim Approval Status'[Approval Status] = "A" ,
    REMOVERFILTER('Columns that do not want to be affected')
)

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Try like

    CALCULATE (
    DISTINCTCOUNT ( SC[Number]
    ),
    FILTER('Dim Approval Status', 'Dim Approval Status'[Approval Status] = "A" )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tried your suggestion and it returned the same result. I'm trying to figure out how to share the sample data workbook and PBIX with you since I can't seem to share the OneDrive link because my organization doesn't let me select the "Anyone with this link" option.

      Interestingly enough, when I tried the measures with just the SC[Number] and the Approval Status in the Sample Data, I found that the numbers were correct there.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Measure will inact with other slicers, table visual, bar visual in the same page or other columns in the same table visual with this measure. You can use removefilters() to remove the column you do not want to filter by it.

     

    You can try some code like the following:

    CALCULATE ( 
    DISTINCTCOUNT ( SC[Number]),
    'Dim Approval Status'[Approval Status] = "A" ,
    REMOVERFILTER('Columns that do not want to be affected')
)

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.