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
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
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.

Top Solution Authors