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
    10 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

Jahspi's avatar
Jahspi
Regular Visitor
6 years ago

Hi !

 

I'm a new to PowerBI and I can't find a proper formula to count number of occurance from a filtered value.
I have two tables :


Table A :

-PermissionID (PrimaryKey) with only distinct values

Table B:

-PermissionID (ForeignKey) with non disitinct values

-AreaFrom (blank or not)

I would like to add a calculate column in Table A to count the number of time the AreaFrom is blank for each PermissionID (Table A).

I tried something like that but with no sucess : 

 

 

Occurence = 
   COUNTX(
       'public ticket_event_data';
             FILTER(
                'public ticket_event_data';'public ticket_event_data'[area_from_id]=BLANK()
                )
   )

 

 

 

Do you have any suggestions ?

Thanks.

 
EILOOP's avatar
EILOOP
Icon for Advocate I rankAdvocate I
6 years ago

Jahspi,

 

It depends on what you are trying to do exactly.

 

There is a DAX command for CountBlank(ColumnName) and then to apply a filter you need to add a Calcualte in front.

 

Something like this Calculate(CountBlank(Column_with_Blanks),Filter(Table_ID,Table_ID[Primary_Key]=Table_Blank[Blank_ID]))

 

There are other ways to do this as well; but the primary thing to remember is that if you want to filter or get a subset I have found to always put a Calcualte in front.

 

Regards,

EILOOP

 

  • Jahspi's avatar
    Jahspi
    Regular Visitor
    6 years ago

    Hi,

     

    Thanks for you reply. However, the formula since to give me incoherent number.

    CALCULATE(COUNTBLANK('public ticket_event_data'[area_from_id]);FILTER('public ticket_event_data';'public ticket_event_data'[permission_id]='public magic_people'[permission_id]))

    Is there something wrong in this ?


    THanks for your help !
    Good Weekend.