Forum Discussion

ksobota's avatar
ksobota
Regular Visitor
10 years ago
Solved

DAX Function for COUNTIF and/or CALCULATE

Hello,   I am fairly new to Power BI and am even newer to the use of DAX functions. For years I have worked in Excel and know how I would do this there, but am finding the same formula I would use ...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Hi ksobota,

     

    Based on your scenario, you can use the DAX below on your INDIVIDUAL table.

    CountUser = CALCULATE(COUNTA(EMPLOYMENTS[Member_C]),FILTER(ALL(EMPLOYMENTS),EMPLOYMENTS[Member_C]=INDIVIDUAL[User_ID]))

     

     

    Regards,

    Charlie Liao

cartecj's avatar
cartecj
Regular Visitor
9 years ago

I have a similiar issue, but this time i want to replicate =COUNTIF($J$2:J2,J2)

 

So I want the table to count out each of the instances that value is seen.  See below example

 

Kris 1

Kris 2

Kris 3

John 1

Ed 1

Kris 4

Ed 2

 

mamoormasoomi's avatar
mamoormasoomi
Icon for Helper I rankHelper I
8 years ago

Did you find the answer to the question? I have the similar issue. Please see below and let me know please if you can help me to get the solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi All,

     

    I am looking for the similar query. I have created a calculated columns in Power BI. 

     

    We need to calculate “total shipment count” in Power BI. For calculating total shipment count we need to apply two conditions:

     

    1. If the “Plant_Shpto_Shpment_Gross KG” is 0 then shipment count will be 0.
    2. Secondly, we have to compare the consecutive rows of the “Plnt_Ship-to_Shpmt_Mat” column. Please refer the snapshot below to view the formula used to get the desired shipment count in Excel. If the values in the consecutive rows are same, it should return 0 as shipment count

    3. I have used following formula in power BI but its showing error.

       

       

      DAX FormulaCan someone please help me to get the output.

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      It seems that the column Plant_Ship-to_Shipment_Material type is set to Text. Change it to Number

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for prompt reply.

       

      I tried to change the data type but due to "0101_150190142_DRX0062220_1720.91" type of values it's giving error as "Cannot convert value '0101_150190142_DRX0062220_1720.91' of type Text to type Integer".

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Then that's not the column you have to sum. Find the column where you have the numeric value.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi. 

      I have similar situation, but i need distinct count result. 

      Table A: 

      1. Employee Name-column

      Table B:

      1. Country

       

      Objective: Count distinct Employee Name by Germany and France- ONLY. 

       

      • Below is what I have done.
      Measure = calculate(DISTINCTCOUNT('tABLE A'[Employee Name]; FILTER('Table B';'Table B (2)'[Country]="Germany" && Table B (2)'[Country]="France.
      • Error: Too many arguments passed to DISTINCTCOUNT function.Maximum argument count for the function is 1. 
      Can anyone help define the write function?
       
      Thank you in advance. 
      • EILOOP's avatar
        EILOOP
        Icon for Advocate I rankAdvocate I
        6 years ago

        Hopefully this time the forum will update (Attemp 6 to upload result)

         

        Here is what you asked for:

        CALCULATE(DISTINCTCOUNT('Table'[Employee]),FILTER('Table','Table'[Country]="Germany" || 'Table'[Country]="France"))

         

        I dont think you initiall closed out your DistinctCount which tossed the original error.

         

        If you need a single value for a KPI Card or Widget then you can also accomplish this through adding the KPI card to a report. Making the employee the vlaue and then adding country as filter to the KPI card and filter the visual to only Germany and France.

         

        Regards.