Filter upon a calculated column or build a calculated column on a calculated column.

[Shliim_1]

Hi, 

I have a large table that looks something like this: 

Latest_record_count	Number_of_columns_filled	clientID	as_of_date	data_about_client
3	23	1
4	22	1
1	22	2
2	22	2
2	22	3
3	23	3

The column "Latest_record_count" returns the most recent row and the column. "Number_of_columns_filled" gives me the most complete dataset (the higher the value, the more recent the data).

A lot of preprocessing is done in Python, but further preprocessing is out of the question due to the need to keep track of historical occurrences.

I need Power BI to return, for every ID, the row with the maximum number of rows filled. Then, based on that, I can filter the dataset to find the maximum value that occurs within this filtered dataset.

It's possible for the maximum value in "Latest_record_count" to be lower than the absolute maximum value of the latest record count.

I want to obtain a table that shows the following:

Latest_record_count		Number_of_columns_filled	clientID	as_of_date	data_about_client
3		23	1
4		22	1
1		22	2
2		22	2
2		22	3
3		23	3

Whenever I do these steps manually in excel and compare this in a table generated in PowerBI I get totally different results. 

this because I cant get PowerBI filter upon an already filtered datatable.  Moreover this in total needs to function as a filter and not as a table. 

I eventually want to calculate multiple things like: distintcounts, averages etc based on this filter where there will be a visual that even more cuts into the data with the as_of_date column (BTW the as_of_data is in principle the same as Latest_record_count) but for certain calculations it makes it way easier to use integers. 

hopefully someone could help me. 

[tamerj1 · Accepted solution]

@Hi @Shliim_1 

Please refer to attached sample file for the proposed solution

FilterMeasure = 
COUNTROWS (
    FILTER ( 
        'Table',
        MAXX (
            TOPN ( 
                1,
                CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[clientID] ) ),
                'Table'[Number_of_columns_filled]
            ),
            'Table'[Latest_record_count]
        ) = 'Table'[Latest_record_count]
    )
)

[Shliim_1]

Thx!!! works like a charm 😃