Replicating Excel COUNTIF w/ DAX formula errors

[Anonymous]

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 2	Alexandria	AO	2
Region 2	Alexandria	Crane	0
Region 2	Alexandria	CRO	0
Region 2	Alexandria	EO	0
Region 2	Alexandria	UO	0

Here is what I am getting in Power BI

Region     Facility        Position Non Qual

Region 2	Alexandria	AO	14
Region 2	Alexandria	Crane
Region 2	Alexandria	CRO	14
Region 2	Alexandria	EO	14
Region 2	Alexandria	UO	14

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.

[TeigeGao]

Hi @Anonymous ,

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

[Anonymous]

@Anonymous  - 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

[Anonymous · Accepted solution]

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]))))