DAX to Support Measure or Column with a filter Where Number <1

[DebbieE]

I have the following as an example

Ref               Person          Stage                Fact

1                   122              1                       0.33

1                    122             2                       0.33

1                   122              3                       0.33

2                   44                1                       1

3                   67                 1                      1

I want a measure (or column) That I can drag in to show the Summed Number of Facts where the fact is less that 1. Because if its less that one it means that Person is against the ref more than once.

In SQL Id do SELECT SUM(Fact) FROM TABLE  WHERE Fact<1 But I cant quite figure out how to do it in DAX.

I tried to add it seperately as a filter but it doesnt seemt to work

[gooranga1 · Accepted solution]

I have attached 2 screenshots below. Not sure if that is what you are looking for?

[DebbieE]

Thanks

Ill see what I can do with that

[arndtvissing]

this is called Binning. you can read more on how to use it here:

https://community.powerbi.com/t5/Desktop/Need-help-in-creating-Histogram-chart-in-power-BI/td-p/4827...

and if the binning function is not working for you, you can try the SWITCH function like below

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

[DebbieE]

Im sorry I dont really understand how to apply those examples to what Im Want to do.

For example I want a Card showing Number of None Multiples 2 And Number of Multiples 1 for this example

---

[parry2k]

@DebbieE I guess you are looking for a sum of first 3 records which will be .99, is that correct?

[DebbieE]

It should sum to 1. Ive just shown as 2 digits. This is a screenshot