topic Re: Formula: Greater than a value inclusive for multiple conditions in DAX Commands and Tips

Formula: Greater than a value inclusive for multiple conditions

jrdn03 — Fri, 09 Dec 2022 17:39:26 GMT

Can't seem to figure this one out....
I have a custom column that I will use as a slicer. Effectively, the column should give me the following:
>$1M
>$2.5M
>$5M
>$10M
So if the Grand Total is $6.7M it should appear in three of the above conditions (in a slicer).

However, I can't get the formula right... I don't want ranges (i.e. >$1M and <$2.5M). I'll need them to be inclusive and apply to any and all of the categories listed previous.

Thanks in advance!

Re: Formula: Greater than a value inclusive for multiple conditions

amitchandak — Sat, 10 Dec 2022 06:46:09 GMT

jrdn03 , I think you are looking for

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Re: Formula: Greater than a value inclusive for multiple conditions

Anonymous — Mon, 12 Dec 2022 01:51:30 GMT

Hi jrdn03 ,

I created some data:

Here are the steps you can follow：

1. Enter data – create a table .

2. Create measure.

Amount_measure = SUMX(FILTER(ALL('Table'),'Table'[Group] <> "G"),[Amount])

Flag = var _select=SELECTCOLUMNS(FILTER(ALL(Slicer_Table),'Slicer_Table'[Value] <= [Amount_measure]),"1",[Slicer]) return IF( SELECTEDVALUE('Slicer_Table'[Slicer]) in _select,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

Re: Formula: Greater than a value inclusive for multiple conditions

jrdn03 — Mon, 12 Dec 2022 17:47:48 GMT

Hi, Sorry I'm not sure if this solution will work for my case. I will rephrase the question. Let me know if you above solution is still the recommended course of action.

I have a table with many projects. Each has a Total Cost. I want to find a way to filter these cost values using "greater than" logic. So for example, the table will have an item that is $1.1M, $3.4M, and $11M. The ranges are:
>$1M, >$2.5M, >$5M, >$10M

I want a project with a total cost of $3.4M to appear with BOTH the >$1M and >$2.5M filter. I intend to use these as slicers. Likewise, an $11M project will appear when any of the ranges are selected.

If it is relevant, I will be viewing the resulted data in a table along with the project name and other info.

Re: Formula: Greater than a value inclusive for multiple conditions

jrdn03 — Fri, 16 Dec 2022 00:18:11 GMT

Hi, Sorry I didn't have luck with this solution... I will rephrase the question. Let me know if you above solution is still the recommended course of action.

I have a table with many projects. Each has a Total Cost. I want to find a way to filter these cost values using "greater than" logic. So for example, the table will have an item that is $1.1M, $3.4M, and $11M. The ranges are:
>$1M, >$2.5M, >$5M, >$10M