Countif in Power BI

[p-ha]

Hi all, I'm fairly new to Power BI and still trying to grasp the logic behind all the DAX and how to utilize them. Any help is greatly appreciated!

Here is my sample data table:

PO	Item #	Description	Location	Year	Supplier
100	AA1	Apple-Green	STA010	2017	Supplier A
101	AA2	Apple-Red	STA010	2018	Supplier A
102	BB1	Banana-Yellow	STA010	2017	Supplier B
103	BB1	Banana-Yellow	STA020	2018	Supplier A
104	BB2	Banana-Green	STA030	2019	Supplier C
105	BB2	Banana-Green	STA040	2017	Supplier A
106	BB3	Banana-Pink	STA030	2019	Supplier B
107	BB3	Banana-Pink	STA010	2017	Supplier B
108	CC1	Candy-Green	STA010	2018	Supplier A
109	CC2	Candy-Red	STA010	2019	Supplier A
110	CC3	Candy-Yellow	STA020	2018	Supplier C
111	CC4	Candy-Blue	STA030	2017	Supplier B
112	DD1	Doll-Green	STA010	2019	Supplier A
113	DD1	Doll-Green	STA030	2018	Supplier B
114	DD2	Doll-Blue	STA020	2017	Supplier C
115	DD2	Doll-Blue	STA040	2018	Supplier C
116	DD3	Doll-Red	STA010	2019	Supplier A
117	DD3	Doll-Red	STA040	2018	Supplier B
118	DD4	Doll-Pink	STA010	2017	Supplier A
119	DD4	Doll-Pink	STA030	2018	Supplier A
120	DD5	Doll-Yellow	STA030	2019	Supplier C
121	DD5	Doll-Yellow	STA020	2017	Supplier A
122	DD5	Doll-Yellow	STA040	2019	Supplier C

What I'm trying to achieve: (below is the result I got from Excel by running pivot table counting total PO per year per each item and countif on the side table)

My goal: 

- count/% of total item with transaction (PO) > 2 in each year and illustrate on canvas as the sum table (right) below.

	2017	2018	2019	G. Total			item with transaction > 2
AA1	1			1		2017	0
AA2		1		1		2018	0
BB1	1	1		2		2019	1
BB2	1		1	2
BB3	1		1	2
CC1		1		1
CC2			1	1
CC3		1		1
CC4	1			1
DD1		1	1	2
DD2	1	1		2
DD3		1	1	2
DD4	1	1		2
DD5	1		2	3

Thank you in advance for your help!

New.Bee.P.

[dax · Accepted solution]

Hi p-ha,

According to your description, it seems that you want to get below result, right(if count(po)>1, then show 1 in Year)?

If so, you could use below measure to see whether it work or not

Measure 5 =
IF (
    CALCULATE (
        COUNT ( test[PO] ),
        FILTER (
            test,
            test[Item #] = MAX ( test[Item #] )
                && test[Year] = MAX ( test[Year] )
        )
    ) > 1,
    1,
    0
)

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Item #]),Data[Item #],"ABCD",COUNTROWS(Data)),[ABCD]>1))+0

Hope this helps.

[dax · Accepted solution]

Hi p-ha,

According to your description, it seems that you want to get below result, right(if count(po)>1, then show 1 in Year)?

If so, you could use below measure to see whether it work or not

Measure 5 =
IF (
    CALCULATE (
        COUNT ( test[PO] ),
        FILTER (
            test,
            test[Item #] = MAX ( test[Item #] )
                && test[Year] = MAX ( test[Year] )
        )
    ) > 1,
    1,
    0
)

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.