cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## CountIFS With Multiple Criterion

Hi All,

I want to use the CountIfs formula of Excel in Power BI for 3 criterias, which are Name, Company Name and Date. However, I am unable to do so in Power BI.

I am using the following formula in Power BI:

Count = Calculate(CountA(Table[Name]),Filter(Table), Table[Company Name],Table[Date])

After using the above formula, it says that there are too few arguments passed to the Filter function. The minimum argument is 2.

I am clueless as to what it means. Kindly please provide a solution or some other alternative for my problem.

Thank you,

Regards,

5 REPLIES 5
Helper I

I tried to Group By the columns : Name, Company and Date. In this however, the rows get reduced. So I was thinking if there is any formula to count the number of particular names?

Here, since there are 4 rows, it is dividing by 4. However, I want only 2 counts for Ash and 1 each for Josh and Ray respectively. Is there any method to perform this?

Thank you,

Regards,

Super User

@saumitra , Create three columns like

countif= countx(filter(Table, [name] =earlier([name]) && [company] =earlier([company] && [Date] <=earlier([Date])), [name])

HC = if([countif] <=1,1,0)

HC1 = divide([HC],Sumx(filter(Table, [name] =earlier([name])), [HC]))

Helper I

Hi Amit,

Tried what you said but getting this error. Correct me if possible.

Thanks!

Super User

@saumitra , what is the filter for company and date ?

example meausre

Count = Calculate(CountA(Table[Name]),Filter(Table , Table[Company Name] = "ABC" && Table[Date] = date(2020,1,1)))

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

Helper I

Hi Amit,

This is a sample of Excel data. These are the formulae I want to use in Power BI.

I hope this helps.

Thanks,

Regards,