In DAX, how to use TOPN but still be a SLICER-RESPONSIVE measure (like KEEPFILTERS)

Question

I created multiple visualizations in this Power BI project, driven by DAX measures that are successfully filter-responsive: when I use a filter (or slicer) on certain columns in the table, all visualizations change.

That filter-responsiveness was created by making sure all the needed data-tables contain certain flags, and then creating a "driver-table" for each flag. Then I created a "relationship" for each flag, between the flag's driver-table and each data-table.

So that way, when I define an "all pages" filter for that flag, I make the filter select FROM THE DRIVER-TABLE. Therefore, that flag selection selects all data-tables, affects all their measures, and therefore changes ALL THEIR VISUALIZATIONS.

But ... that's not working, for my "bottom 10" visualizations. The problem is: I've been asked to exclude the rows with ZERO counts.

I tried using the Power BI filter feature, to filter by TOP N, but then the zero count rows are selected.

I tried using TOPN in DAX for a table, but the problem with this, is that it's not filter-responsive: KEEPFILTERS does not seem to be doing its job.

Here's an example that I made generic, of code like I tried, using TOPN:

TopN_table = 
    TOPN(5, 
        SUMMARIZE(
            FILTER(
                KEEPFILTERS('my_detail_data')
                , 'my_detail_data'[flag_a_4_member] = "Y" 
                && 'my_detail_data'[flag_b_4_line] = "Y"
            ),
            'my_detail_data'[text_col_to_group_1],
            'my_detail_data'[text_col_to_group_2],
            "summ_col_1", SUM('my_detail_data'[int_col_1]),
            "summ_col_2", SUM('my_detail_data'[int_col_2])
        ),
        [summ_col_1], DESC,
        [summ_col_2], DESC
    )

I've also tried a variation where the DAX table does NOT do TOPN, but just does the basic filtering and the exclusion of zero counts, but again ...... KEEPFILTERS doesn't seem to be working: it's not filter-responsive.

Here's an example like that code, that does almost everything, leaving the TOPN to a filter:

DaxTbl_2 = 
  VAR temp_table = 
    FILTER(KEEPFILTERS(
        SUMMARIZE(
            FILTER(KEEPFILTERS('my_detail_data')
                , 'my_detail_data'[flag_a_4_member] = "Y" 
                && 'my_detail_data'[flag_b_4_line] = "Y"),
            'my_detail_data'[text_col_to_group_1],
            'my_detail_data'[text_col_to_group_2],
            "summ_col_1", SUM('my_detail_data'[int_col_1]),
            "summ_col_2", SUM('my_detail_data'[int_col_2])
        )), 
        [summ_col_1] > 0
    )
RETURN 
    temp_table

I'm guessing the lack of filter-responsiveness is because SUMMARIZE doesn't include the flag-columns. But those flags are to be selected at the detail level (in the pseudo-code above, at the level of [int_col_1] and [int_col_2]), whereas I need to exclude zero-counts at the group level ([summ_col_1]).

Suggestions?

Answer 1

I agree that using a calculated table is not the right approach to solving this problem. I recommend using a series of measures on the table: the measures will automatically updates based on the slicers that are selected.

Here's a summary of the steps I recommend taking:

1. Create summing measures for int_col_1 and int_col_2.
2. Create ranking measures for int_col_1 and int_col_2.
3. Create an overall ranking measure.
4. Add overall ranking to table and filter the table by top 5 from overall ranking measure AND overall ranking is not blank.
5. Test by selecting slicers and seeing how the table adjusts.

1 Create summing measures for int_col_1 and int_col_2.

sumCol1 = SUM(my_detail_data[int_col_1])

sumCol2 = SUM(my_detail_data[int_col_2])

2 Create ranking measures for int_col_1 and int_col_2.

col1Rank = 
VAR rankVal = 
RANKX (
        ALLSELECTED (
            my_detail_data[text_col_to_group_1],
            my_detail_data[text_col_to_group_2]
        ),
    [sumCol1],
    ,
    ASC,
    DENSE
)

RETURN IF([sumCol1] <> 0, rankVal, BLANK())

col2Rank =
DIVIDE (
    RANKX (
        ALLSELECTED (
            my_detail_data[text_col_to_group_1],
            my_detail_data[text_col_to_group_2]
        ),
        [sumCol2],
        ,
        DESC,
        DENSE
    ),
    COUNTROWS ( ALL ( my_detail_data ) )
)

Make sure both are Decimal number format with 2 decimal places

3 Create an overall ranking measure.

FinalRank = 
VAR rankVal = RANKX (
    ALLSELECTED (
        my_detail_data[text_col_to_group_1],
        my_detail_data[text_col_to_group_2]
    ),
    [col1Rank] + [col2Rank],
    ,DESC
)

RETURN IF([sumCol1] <> 0, rankVal, 9999)

4 Add overall ranking to table and filter the table by top 5 from overall ranking measure AND overall ranking is not blank.

5 Test by selecting slicers and seeing how the table adjusts.

Original table on left and ranked table on the right

---

Mock data used for this answer:

text_col_to_group_1	text_col_to_group_2	flag_a_4_member	flag_b_4_line	int_col_1	int_col_2
GroupA	Type1	Y	Y	100	200
GroupA	Type2	Y	N	150	100
GroupB	Type1	N	Y	120	80
GroupB	Type2	Y	Y	200	50
GroupC	Type1	Y	Y	90	30
GroupC	Type2	N	N	50	60
GroupD	Type1	Y	Y	180	110
GroupD	Type2	N	Y	140	90
GroupE	Type1	Y	Y	170	220
GroupE	Type2	Y	N	60	70
GroupA	Type1	Y	Y	300	500
GroupA	Type2	Y	N	250	150
GroupB	Type1	N	Y	320	180
GroupB	Type2	Y	Y	400	250
GroupC	Type1	Y	Y	190	130
GroupC	Type2	N	N	150	160
GroupD	Type1	Y	Y	280	210
GroupD	Type2	N	Y	240	190
GroupE	Type1	Y	Y	370	320
GroupE	Type2	Y	N	160	170
GroupF	Type1	Y	N	0	0
GroupF	Type2	N	N	0	0
GroupG	Type1	N	N	0	1
GroupH	Type2	N	N	1	0