Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
saumitra
Helper I
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,

Saumitra Upadhyaya

5 REPLIES 5
saumitra
Helper I
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? 

 

saumitra_0-1614075226308.png

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? 

Kindly please help. 

 

Thank you,

Regards,

Saumitra Upadhyaya

amitchandak
Super User
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]))

Hi Amit, 

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

 

saumitra_0-1614069530734.png

Thanks! 

amitchandak
Super User
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.

Hi Amit,

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

saumitra_0-1614061450413.pngsaumitra_1-1614061682825.pngsaumitra_2-1614061713929.png

I hope this helps.

 

Thanks,

Regards,

Saumitra Upadhyaya

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.