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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filtering and Ranking

Hello, To be honest, i even don't have a clue on how to define a correct subject for this problem.

I'll explain, please consider this table

TableTable

The idea is that we know by day what was the maximum out of order machine we had, we also know for that day how many machines where operational, so we can calculate the ratio. We also know the max time all the machines where simultaneous out of order.

Now is the goal that depending on the date selection we would by machine find the record with the highest Ratio.

But we would also want to display the corresponding MaxOutOofOrder and the Time

This means that when there is no date selection the report should show the first table and if 2/6/2017 is selected the second.

2017-06-06 19_41_19-Microsoft Excel - Book1  [Compatibility Mode].png

 

Ok The be even more demanding, it would be nice that in case 2 days have the same Ratio then we display the one woth the highest Time ...

 

Pleas feel free to help, kind regards, Harry

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

Current power bi not support to create a dynamic calculate column/table based on filter/slicer.

 

In my opinion, I'd like to suggest you convert these columns to measure and use original location column as the group to calculate the related value.

 

Sample measures:

MAX OutOfOrder = MAX(Sheet2[MaxOutOfOrder])
	
MAX Day = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
return
MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day])

Related TotalOperational = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
var maxDay=MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day]) 
return
LOOKUPVALUE(Sheet2[TotalOperational],Sheet2[Location],currentLocation,Sheet2[MaxOutOfOrder],maxOrder,Sheet2[Day],maxDay)

Related Ratio = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
var maxDay=MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day]) 
return
LOOKUPVALUE(Sheet2[Ratio],Sheet2[Location],currentLocation,Sheet2[MaxOutOfOrder],maxOrder,Sheet2[Day],maxDay)

Related Time = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
var maxDay=MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day]) 
return
LOOKUPVALUE(Sheet2[Time],Sheet2[Location],currentLocation,Sheet2[MaxOutOfOrder],maxOrder,Sheet2[Day],maxDay)

 

1.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

You can refer to below formula to achieve your requirement:

 

Steps:

1. Use summarize function to group records to find the "MaxOutOofOrder" of each "Location".

2. Search for the max day by "Location" and "MaxOutOofOrder".

3. Use lookupvalue function to find out other columns based on above thress columns.

 

Table formula:
summaryTable = SELECTCOLUMNS( ADDCOLUMNS( SUMMARIZE(Sheet2,[Location], "MaxOutOfOrder",MAX(Sheet2[MaxOutOfOrder])), "Day",CALCULATE(MAX(Sheet2[Day]),FILTER(ALL(Sheet2),Sheet2[Location]=EARLIER([Location])&&Sheet2[MaxOutOfOrder]=EARLIER([MaxOutOfOrder])))), "Location",[Location], "Day",[Day], "MaxOutOfOrder",[MaxOutOfOrder], "TotalOperational",LOOKUPVALUE(Sheet2[TotalOperational],Sheet2[Location],[Location],Sheet2[Day],[Day],Sheet2[MaxOutOfOrder],[MaxOutOfOrder]), "Ratio",LOOKUPVALUE(Sheet2[Ratio],Sheet2[Location],[Location],Sheet2[Day],[Day],Sheet2[MaxOutOfOrder],[MaxOutOfOrder]), "Time",LOOKUPVALUE(Sheet2[Time],Sheet2[Location],[Location],Sheet2[Day],[Day],Sheet2[MaxOutOfOrder],[MaxOutOfOrder]))

 

1.PNG

Regards,

 

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi Xiaoxin Sheng,

 

Wonrdefull, but this is static, the calculated table is 'created' when loading the data and doesn't change when filtering is applied.

But I will give it a try based on your DAX formulas ..

Hi @Anonymous,

 

Current power bi not support to create a dynamic calculate column/table based on filter/slicer.

 

In my opinion, I'd like to suggest you convert these columns to measure and use original location column as the group to calculate the related value.

 

Sample measures:

MAX OutOfOrder = MAX(Sheet2[MaxOutOfOrder])
	
MAX Day = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
return
MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day])

Related TotalOperational = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
var maxDay=MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day]) 
return
LOOKUPVALUE(Sheet2[TotalOperational],Sheet2[Location],currentLocation,Sheet2[MaxOutOfOrder],maxOrder,Sheet2[Day],maxDay)

Related Ratio = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
var maxDay=MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day]) 
return
LOOKUPVALUE(Sheet2[Ratio],Sheet2[Location],currentLocation,Sheet2[MaxOutOfOrder],maxOrder,Sheet2[Day],maxDay)

Related Time = 
var currentLocation = LASTNONBLANK(Sheet2[Location],Sheet2[Location])
var maxOrder=MAXX(FILTER(ALLSELECTED(Sheet2),Sheet2[Location]=currentLocation),[MaxOutOfOrder])
var maxDay=MAXX(FILTER(ALLSELECTED(Sheet2),[Location]=currentLocation&&[MaxOutOfOrder]=maxOrder),[Day]) 
return
LOOKUPVALUE(Sheet2[Time],Sheet2[Location],currentLocation,Sheet2[MaxOutOfOrder],maxOrder,Sheet2[Day],maxDay)

 

1.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Great stuff, Thx.Man Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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