topic Re: How to create a rank column using a boolean value column, by occurence of 'TRUE' in ordered table? in DAX Commands and Tips

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

saralsaral — Fri, 13 Aug 2021 13:07:05 GMT

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.

Group	Time_Index	Category	event_bool	Some_Plotting_Value_Col	Expected_ranking_output	Expected_Filled_Output
A	1	other	FALSE	0.929	0	0
A	2	foo	FALSE	0.929	0	1
A	3	foo	FALSE	0.209	0	1
A	4	foo	FALSE	0.191	0	1
A	5	foo	TRUE	0.071	1	1
A	6	foo	TRUE	0.507	1	1
A	7	foo	TRUE	0.101	1	1
A	8	foo	TRUE	0.661	1	1
A	9	foo	FALSE	0.936	0	1
A	10	foo	FALSE	0.611	0	1
A	11	foo	TRUE	0.397	2	2
A	12	foo	TRUE	0.677	2	2
A	13	foo	TRUE	0.407	2	2
A	14	foo	FALSE	0.473	0	2
A	15	foo	FALSE	0.411	0	2
A	16	foo	FALSE	0.012	0	2
A	17	bar	TRUE	0.080	0	0
A	18	bar	TRUE	0.149	0	0
A	19	bar	TRUE	0.748	0	0
A	20	bar	TRUE	0.812	0	0
B	1	foo	FALSE	0.734	0	1
B	2	foo	FALSE	0.917	0	1
B	3	foo	TRUE	0.734	1	1
B	4	foo	TRUE	0.755	1	1
B	5	foo	TRUE	0.854	1	1

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

Anonymous — Thu, 12 Aug 2021 13:13:21 GMT

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.

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

saralsaral — Thu, 12 Aug 2021 14:06:59 GMT

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?

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

Anonymous — Thu, 12 Aug 2021 15:31:48 GMT

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?

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

saralsaral — Thu, 12 Aug 2021 17:07:41 GMT

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

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

Jos_Woolley — Thu, 12 Aug 2021 20:02:04 GMT

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

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

saralsaral — Fri, 13 Aug 2021 12:48:01 GMT

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)