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

How to create a rank column using a boolean value column, by occurence of 'TRUE' in ordered table?

I have a time indexed table with a `Group`, a `Category` , and a `event_bool` . I need to plot stuff against the boolean variable, because whenever `TRUE` value occurs it is an `event` in the timeline that needs to be analyzed one against the other. After the ranking is done, I also want to fill the ranking in a way that only the `Category` variable with value `foo` gets the rank value. Thus the event time range is "expanded" out based on `Category` variable. The last two columns are expected outputs.


Edit: Ranking is to be done only when Cateogry='foo' 

Edit: sample pbix file: https://drive.google.com/file/d/1mPlsGJ1YR88YfyKkIgYBTIZuGyBDT38S/view?usp=sharing

I have previously done `Expected_ranking_output` in python, and was planning to plug in the python script with power query; however, I read in the docs that you have make the dataset public if you want to python with power query (bemused by this). Not sure if my organization will allow that. 

 

GroupTime_IndexCategoryevent_boolSome_Plotting_Value_ColExpected_ranking_outputExpected_Filled_Output
A1otherFALSE0.92900
A2fooFALSE0.92901
A3fooFALSE0.20901
A4fooFALSE0.19101
A5fooTRUE0.07111
A6fooTRUE0.50711
A7fooTRUE0.10111
A8fooTRUE0.66111
A9fooFALSE0.93601
A10fooFALSE0.61101
A11fooTRUE0.39722
A12fooTRUE0.67722
A13fooTRUE0.40722
A14fooFALSE0.47302
A15fooFALSE0.41102
A16fooFALSE0.01202
A17barTRUE0.08000
A18barTRUE0.14900
A19barTRUE0.74800
A20barTRUE0.81200
B1fooFALSE0.73401
B2fooFALSE0.91701
B3fooTRUE0.73411
B4fooTRUE0.75511
B5fooTRUE0.85411
6 REPLIES 6
Jos_Woolley
Solution Sage
Solution Sage

Hi,

You don't mention which values from the Category column are to be considered. It appears to be for 'foo' only, though can you please clarify? If not, and other entries in that column are also to be considered, can you explain why the four entries of 'bar' have Expected_ranking_output=0 (despite having event_bool=True)?

Regards

Yes, its for 'foo' only. The ranking is to be done only when Category is `foo`. The event_bool can be true when Category is not equal to 'foo', but I don't want to rank those values. The susbset of the data to rank is 1) The Category='foo' and event_bool=TRUE. So basically event_bool  may be ignored in interest of something like this: 

rank_able = IF(AND(Sheet1[Category]="foo", Sheet1[event_bool]=TRUE()),1,0)

 

Anonymous
Not applicable

I'm not bemused in the least. There are valid reasons behind this decision.

 

To the point... Why can't you do this - seemingly not too complex task - in Power Query WITHOUT using Python? Power Query has a powerful functional language called M where you can calculate about anything.

It might not be complex for you, it is for me. I am not comfortable with Power Query. I have extensive experience in Python, and still it took me almost half a day to do this task after fetching the data from ADX. Can you point me somewhere, is there a similar example? Are there some keywords that might help narrowing my search? 

Anonymous
Not applicable

@saralsaral 

 

I would have given you the M code to do it by now, had I been able to fully make sense of what you've described above. But it's not possible (at least for me). So, the upshot is this. Please try to describe your algorithm as clearly as possible, give me a good, representative set of your data, show the input and the expected output (I know you've started above but you have to be maximally clear since you know what you want to do, we don't and we don't know the ins and outs) and I'll try to write the code? How does this sound?

I'll try describing the problem more clearly. Thank you

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors