cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Filtering rows based on cascading sequence

Hi there

 

I have data in the format given below.

 

Sample IDModelSK
BH00345

mm23

0.0290.443
BH00345mm150.0220.018
BH00345mm140.0471.228
BH00345

gm23

0.040.669
BH00345gm150.0572.709
BH00345gm140.0420.883
BH00746

mm24

0.1130.32
BH00746mm230.0460.11
BH00746mm150.0190.658
BH00746

gm24

0.0230.129
BH00746gm230.0190.127
BH00746gm150.1440.031
BH00964mm240.1750.519
BH00964mm230.1410.513
BH00964gm240.1710.403
BH00964gm230.1670.031
BH00583mm230.0390.029
BH00583gm230.0640.115

 

Samples have data for both mm and gm model types and for several iterations of each model as indicated by the model suffix number. 

Newer samples do not contain data for all older models. 

Older samples do not contain data for all newer models. 

 

I want to condense the table so that the Sample ID column contains unique values, and each row contains data from the most recent mm model. 

 

eg. for the above table, I would like the output to give

Sample IDModelSK
BH00345

mm23

0.0290.443
BH00746

mm24

0.1130.32
BH00964mm240.1750.519
BH00583mm230.0390.029

 

Full data set contains 3554 rows and 747 unique sample ID codes

 

Thanks for your help

 

<edit. sorry for the horrendous table!! I cant seem to change the width of the columns>

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @mef47 ,

 

We can use the Power Query Editor to meet your requirement.

 

1. Filter the Model column, delete the rows that contain “gm”.

 

F1.jpg

 

2. We sort the sample ID Ascending, and sort the Model Descending.

 

F2.jpg

 

3. Then we need to Group the Sample ID column.

 

F3.jpg

 

4. And we can add a custom column to add an index column. Each group has an index column.

 

F4.jpg

 

5. Delete the first two columns. And expand the last column.

 

F5.jpg

 

6. At last we need to filter the Rank column equal 1, then delete the rank column.

 

F6.jpg

 

F7.jpg

 

The result table just has four rows.

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi @mef47 ,

 

We can use the Power Query Editor to meet your requirement.

 

1. Filter the Model column, delete the rows that contain “gm”.

 

F1.jpg

 

2. We sort the sample ID Ascending, and sort the Model Descending.

 

F2.jpg

 

3. Then we need to Group the Sample ID column.

 

F3.jpg

 

4. And we can add a custom column to add an index column. Each group has an index column.

 

F4.jpg

 

5. Delete the first two columns. And expand the last column.

 

F5.jpg

 

6. At last we need to filter the Rank column equal 1, then delete the rank column.

 

F6.jpg

 

F7.jpg

 

The result table just has four rows.

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

Super User II
Super User II

Hi @mef47  -

 

Presuming that the "latest" model will be the MM with the highest number, we can create the following 3 measures:

 

Latest Model = CALCULATE(MAX(Samples[Model]))

Latest K =
VAR __model = [Latest Model]
VAR __sample =
    SELECTEDVALUE ( Samples[Sample ID] )
RETURN
    LOOKUPVALUE (
        Samples[K],
        Samples[Model], __model,
        Samples[Sample ID], __sample
    )

Latest S =
VAR __model = [Latest Model]
VAR __sample =
    SELECTEDVALUE ( Samples[Sample ID] )
RETURN
    LOOKUPVALUE (
        Samples[S],
        Samples[Model], __model,
        Samples[Sample ID], __sample
    )

 

Create a table visual, put Sample ID as the first value, then the 3 measures:

 

2020-08-10 11_19_53-N_and_BASE_problem (3) - Power BI Desktop.png

 

Hope this helps,

David

 

 




Proud to be a Super User!




Hi David

Not quite what I was after but helpful enough to get me to where I wanted to go.

The ALLEXCEPT function what what I was after

 

I ended up going with:

LATEST_MODEL = CALCULATE(MAX('Samples'[MODEL]),ALLEXCEPT('Samples','Samples'[Sample ID]))
 
Thanks
 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors