Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RielLife
Frequent Visitor

How to Filter table based on Max value of column

 

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])
                ) )


Naamloos.png

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. 

7 REPLIES 7
v-danhe-msft
Employee
Employee

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:

1.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.