Function Excell Countifs in Power BI

[Julianvesga]

Hi Team, i need to found tha result of the following formula in power BI:

COUNTIFS($A$3:$A$16,$E6,$C$3:$C$16,"<="&E$2)

	A	B	C	D	E
1
2	Step	Trasactions	time		0.1
3	Input	1	0.1
4	Authorize	3	0.1
5	Input	2	0.2
6	Input	3	0.1		Input	4
7	Repair	2	0.2		Authorize
8	Input	5	0.3		Repair
9	Input	6	0.1
10	Repair	3	0.2
11	Authorize	1	0.3
12	Input	4	0.1
13	Repair	1	0.1
14	Authorize	6	0.2
15	Authorize	2	0.3
16	Authorize	4	0.3

[Anonymous]

You can use the following measure:

Count if Mimick = CALCULATE( COUNTROWS( Table1)  , FILTER( Table1, Table1[time] <=.1))

[Julianvesga]

the formula is working but now some transactions do not have time (blank) and the formula counts them as if they were less than 0.2. How could you avoid having blank caillas not be taken into account?

we are close...thank you.

[vivran22 · Accepted solution]

@Julianvesga 

Hi,

Although the formula suggested by @Anonymous  should solve the purpose and should exclude blanks, but you may try this:

CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[time] <= .1 && Table1[time] <>BLANK()))

Thanks,

Vivek

[Julianvesga]

excellent its work, thank you....one questions and i consider that is symple...how i can have relation between two matrix that are in defferents page (big matrix), for example I have a matrix with information about several countries and in the other matrix I have the same countries but with numeric data, and in the first matrix I give click within the matrix to a country, and I want that in the another one show me just this countries ..... this without using filters only with the click. thank you

[vivran22]

@Julianvesga 

Hi,

I am glad the solution worked.

For your second scenario, is it possible for you to demonstrate it with some example... dummy data tables along with the desired output?

Thanks,

Vivek

[Julianvesga]

Hi, yes please, with a example a thing is more easy. 

[HotChilli]

You could use the 'drillthrough' feature.  On Page 2, drag 'country' to the 'Add drillthrough fields here' section.

From Page 1, right-click the country in the matrix and select 'drilthrough'