How to Filter table based on Max value of column

[RielLife]

Hi experts,

I need help with something Im trying to figure out all day...

I have a table as shown in the image below:
I have multiple "medewerkers" as show in the first column, which have a volgnummer (second column to the right, with a urenregistratie (second column to the left) which gives an boolean value. 

I want to filter the table to only show to rows of each "Medewerker" with the highest "volgnummer", so I can know to most current boolean value (uurregistratie). 

With DAX I got to the following code:

Urenregistreren = CALCULATE(
    MAX ( ProfitDirectIndirect[Column1.Volgnummer] );
    FILTER (
        ProfitDirectIndirect;
        ProfitDirectIndirect[Column1.Medewerker] = EARLIER ( ProfitDirectIndirect[Column1.Medewerker])
                ) )

However, by using the custom column it does not show in the query editor and it also does not help me to filter the table...

I am stuck and I think the wrong way I guess... I am very desperated for help. 

[v-danhe-msft]

Hi @RielLife,

Based on my research, the dax formula you have created is based on your model and it is stored in the Power BI enginee, while the data in query editor is underlying data, everything you do in DAX happens after the Query Editor is done with it's work. You need to see these as 2 different steps - first the Query Editor will do its work and then DAX modeling happens on top of it. 

Reference:https://support.office.com/en-us/article/how-power-query-and-power-pivot-work-together-a5f52cba-2150...

If you want to filter the max value of column, you could create a new table with this DAX:

Table = CALCULATETABLE('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren]=2)

Result:

You could also download the pbix file to have a view:

https://www.dropbox.com/s/n80g4aqp1aphlnr/How%20to%20Filter%20table%20based%20on%20Max%20value%20of%...

Regards,

Daniel He

[RielLife]

@v-danhe-msftThank you very much for the explanation and the sugested solution!
One problem is that sometimes the "max"  value of the column volgnummer can be 3, 4 or 5 (thus not only 2).

Do you know how I can adjust the by you provided formula to achieve my desired result?

Table = CALCULATETABLE('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren]=2)

Regards,

RielLife

[#4]

Table = CALCULATETABLE('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren]=2)

[v-danhe-msft]

Hi @RielLife,

You could modify the formula as this:

Table = CALCULATETABLE('ProfitDirectIndirect',FILTER('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren]=MAXX('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren])))

Hope it could help you.

Regards,

Daniel He

[RielLife]

Hi @v-danhe-msft,

Thanks. It comes closes but is not yet what I am looking for. Now it shows in the table only the row of the employee with the highest "volgnummer"... IT now needs to show the highest value by employee.

Thus now I have this:

test = CALCULATETABLE(ProfitDirectIndirect;FILTER('ProfitDirectIndirect';'ProfitDirectIndirect'[Column1.Volgnummer]=MAXX('ProfitDirectIndirect';'ProfitDirectIndirect'[Column1.Volgnummer]))) 

^ but I need an additional filter in the formula to do the max lookup on employee level (column name = employee). 

Hope this is possiblee... 

Best regards and thanks so much in advance,

RielLife

[v-danhe-msft]

Hi @RielLife,

I could not understand the sentence "an additional filter in the formula to do the max lookup on employee level (column name = employee)", could you please post me your desired result if possible?

Regards,

Daniel He

[RielLife]

@Greg_Deckler @v-danhe-msft

https://1drv.ms/u/s!Ap5MNdk_ugWxga0GL5YfLWfrdP9B-g <-- here is a link to the sample set. 

It contains 2 tables: in the ontvagsttabel a column should be added containing the true or false value of the highest "volgnummer"  by medewerker (employee in dutch). Because the highest "volgnummer" value refers to the current correct "registratieuren" column value (true or false). 

Is there a way I can achieve this?

[Greg_Deckler]

Would help to have sample data in a form that can be copied and pasted easily as well as expected results. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, I get the feeling that you probably want to use something like SUMMARIZE in your measure.