create a countif measure not impacted by filter in visual in powerBI

Question

Need help in creating measures that will reflect the actual count of rows in the table when filtered.

Example:

ID	RankC	RankA	Avg Diff	RankC_count	RankA_count	Avg Diff_count
1000	AAA	XYZ	+01.00 to +01.25	5	6	4
1001	AAA	ZY1	+01.5.00 to +01.75	5	1	5
1002	AAB	XYZ	+01.5.00 to +01.75	3	6	5
1003	AAB	ZY2	+01.5.00 to +01.75	3	1	5
1004	AAB	XYZ	+01.00 to +01.25	3	6	4
1005	AAA	XYZ	+01.00 to +01.25	5	6	4
1006	AAA	ZY3	+01.00 to +01.25	5	1	4
1007	AAC	XYZ	+01.25.00 to +01.5	1	6	2
1008	AAA	ZY4	+01.25.00 to +01.5	5	2	2
1009	AAZ	ZY4	+01.5.00 to +01.75	1	2	5
1010	ABY	XYZ	+01.5.00 to +01.75	1	6	5

The last 3 columns represent the count of each entry. If I use the measure such as below, it provides the correct count. However, when I use in the visual, filtering by ID, say ID 1000, I want it to show line 1 with 5,6, and 4 on the counts, instead of all 1.

Questions:

Is there any measure to give me the correct result? say summarize the table first then do a lookup?
is creating a column the only choice? I cannot create columns since I need 1000 of these calculated columns. whereas using measure, I can create 1000 in one go.

Thanks for any help.

AverageDiff_Count =
CALCULATE (
    COUNTROWS (
        FILTER ( '28Jun_1973', [Average Diff] = '28Jun_1973'[Average Diff] )
    )
)

Apology for the table as it does not allow to post if not considered as code — theo
– theo, Commented Jul 15, 2021 at 18:26

Alexis Olson · Accepted Answer · 2021-07-15 19:25:41Z

0

The ALL function is useful here. It removes filter context so that it uses the whole table instead of just the part in the current filter context.

AvgDiff_Count =
VAR CurrAvgDiff = SELECTEDVALUE ( '28Jun_1973'[Avg Diff] )
RETURN
    COUNTROWS (
        FILTER ( ALL ( '28Jun_1973' ), '28Jun_1973'[Avg Diff] = CurrAvgDiff )
    )

answered Jul 15, 2021 at 19:25

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

theo Over a year ago

Awesome. You rock!

Alexis Olson Over a year ago

If this solves your problem, please accept it to mark your question as answered.

Collectives™ on Stack Overflow

create a countif measure not impacted by filter in visual in powerBI

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related