cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
drogers10940 Frequent Visitor
Frequent Visitor

Replicating Excel COUNTIF w/ DAX formula errors

Good Afternoon,

 

I am trying to replicate a COUNTIF formula in excel (=COUNTIF('Employee Data'!J:J,B6&"-"&C6&"-"&"Non Qual")) in Power BI, but I am running into some issues. 

 

Here is my DAX formula:

Non Qual = CALCULATE(COUNTROWS('All User Listing'), 'All User Listing'[OPS Qualification Level (if applicable)] ="Non Qual") 
 
Here is the table in Excel for what Power BI should look like.
Region      Facility        Position Non Qual
Region 2AlexandriaAO2
Region 2AlexandriaCrane0
Region 2AlexandriaCRO0
Region 2AlexandriaEO0
Region 2AlexandriaUO0

 

Here is what I am getting in Power BI

Region     Facility        Position Non Qual

Region 2AlexandriaAO14
Region 2AlexandriaCrane 
Region 2AlexandriaCRO14
Region 2AlexandriaEO14
Region 2AlexandriaUO14

 

So the DAX Formula should be referencing another Table 'All User Listing' and counting the Occurences of "Non Qual" for each Position('All User Listing'[OPS Position Standing (if applicable)]) at each facility('All User Listing'[Facility]). I have been trying to use FILTER and RELATE in the formulas but I cant seem to get those to work at all. So the DAX formula for the Column should call to the table 'All User Listing' and then count the occurance of 'All User Listing'[OPS Qualification Level (if applicable)] =  "Non Qual" for each Position 'All User Listing'[OPS Position Standing (if applicable)]

 

 

Any help or guidance would be helpful. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
drogers10940 Frequent Visitor
Frequent Visitor

Re: Replicating Excel COUNTIF w/ DAX formula errors

The relationships are all set up. Unfortanetly I cant share the pbix due to some of the data. But I was able to get it working with the below formula:

 

Non Qual = if( isblank(CALCULATE(COUNTROWS('All User Listing'), Filter('All User Listing', AND('All User Listing'[OPS Qualification Level (if applicable)] ="Non Qual", 'All User Listing'[OPS Position Standing (if applicable)] = 'Benchmark Chart'[Position])))), 0, CALCULATE(COUNTROWS('All User Listing'), Filter('All User Listing', AND('All User Listing'[OPS Qualification Level (if applicable)] ="Non Qual", 'All User Listing'[OPS Position Standing (if applicable)] = 'Benchmark Chart'[Position]))))
3 REPLIES 3
natelpeterson Senior Member
Senior Member

Re: Replicating Excel COUNTIF w/ DAX formula errors

@drogers10940  - If you're trying to create a Calculated Column in an unrelated table, then it won't work, because there is no filtering based on the Region, etc. You could try it in a Measure, but you would still face the same issue if the tables are not related.

 

I think you need to get the relationships hooked up first. Could you share your pbix?

 

Cheers!

Nathan

TeigeGao Senior Member
Senior Member

Re: Replicating Excel COUNTIF w/ DAX formula errors

Hi @drogers10940 ,

Could you please share the pbix file or sample data and expected result to us for analysis?

The DAX query you shared will count the rows of all items whose '[OPS Qualification Level (if applicable)] ="Non Qual" in your table, each item will have the same value.

Best Regards,

Teige

drogers10940 Frequent Visitor
Frequent Visitor

Re: Replicating Excel COUNTIF w/ DAX formula errors

The relationships are all set up. Unfortanetly I cant share the pbix due to some of the data. But I was able to get it working with the below formula:

 

Non Qual = if( isblank(CALCULATE(COUNTROWS('All User Listing'), Filter('All User Listing', AND('All User Listing'[OPS Qualification Level (if applicable)] ="Non Qual", 'All User Listing'[OPS Position Standing (if applicable)] = 'Benchmark Chart'[Position])))), 0, CALCULATE(COUNTROWS('All User Listing'), Filter('All User Listing', AND('All User Listing'[OPS Qualification Level (if applicable)] ="Non Qual", 'All User Listing'[OPS Position Standing (if applicable)] = 'Benchmark Chart'[Position]))))