cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DebbieE Member
Member

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
gooranga1 Senior Member
Senior Member

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

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

 

Demo2.PNGDemo1.PNG

6 REPLIES 6
arndtvissing Frequent Visitor
Frequent Visitor

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

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

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

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

 


 

Super User
Super User

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

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

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






DebbieE Member
Member

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

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

gooranga1 Senior Member
Senior Member

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

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

 

Demo2.PNGDemo1.PNG

DebbieE Member
Member

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

Thanks

 

Ill see what I can do with that