cancel
Showing results for
Did you mean:  Solution Sage

## DAX

Dear PBI Family ,

It's just 2 months that I have started using power bi I as my daily work now

I am stuck in an issue please consider if you can answer to this question so that it helps me alot

I have a measure which calculates from different fields and gives me a value which can be filtered using date slicer.

Let's call this measure as test

Now my problem is I have to put values which I get from this test measure range wise into a table like for example the expected result is as below

Name       <0.25        0.25-0.50      0.50-0.75     >0.75

A                  1                   2                   66            23

B                 12                 22                   6            28

C                 12                 22                   6            28

Currently I have achieved it using a long way that is I am make four measures and every measures count rows by filtering the table according to the value for example

For <0.25 = Countrows(Filter(test<0.25))

I read somewhere regarding this evaluate dax
Which returns a table so I was thinking if is it possible to get my expected result using evaluate

Note - name colunmn in above expected result is a field from a table students  where values are A,B and C
Please project some light on my question thank you

Regards ,

Sujit

1 ACCEPTED SOLUTION  Community Support

Do you want to count the number of Model or the number of Name?

We can create two measures and use the following ways to meet your requirement.

1. Create a Model column in data table.

``Model = CALCULATE(MAX('index table'[Model]),FILTER('index table','index table'[Name]='data table'[Name]))`` 2. Create a new table.

``Table = CROSSJOIN(VALUES('index table'[Model]),{"<0.25","0.25-0.5","0.5-0.75",">0.75","Total"})`` 3. If you want to count the number of Name, you can use the following measure.

``````Measure =
var _025 = CALCULATE(COUNT('data table'[Name]),FILTER('data table',[Test]<0.25 && 'data table'[Model]=MAX('Table'[Model])))
var _025_05 = CALCULATE(COUNT('data table'[Name]),FILTER('data table',[Test]>=0.25&&[Test]<0.5 && 'data table'[Model]=MAX('Table'[Model])))
var _05_075 = CALCULATE(COUNT('data table'[Name]),FILTER('data table',[Test]>=0.5&&[Test]<0.75 && 'data table'[Model]=MAX('Table'[Model])))
var _075 = CALCULATE(COUNT('data table'[Name]),FILTER('data table',[Test]>=0.75&& 'data table'[Model]=MAX('Table'[Model])))
return
SWITCH(
TRUE(),
MAX('Table'[Value]) = "<0.25",_025,
MAX('Table'[Value]) = "0.25-0.5",_025_05,
MAX('Table'[Value]) = "0.5-0.75",_05_075,
MAX('Table'[Value]) = ">0.75",_075,
MAX('Table'[Value]) = "Total",_025+_025_05+_05_075+_075)`````` 4. If you want to count the number of Model, you can use the following measure.

``````Measure 2 =
var _025 = CALCULATE(DISTINCTCOUNT('data table'[Model]),FILTER('data table',[Test]<0.25 && 'data table'[Model]=MAX('Table'[Model])))
var _025_05 = CALCULATE(DISTINCTCOUNT('data table'[Model]),FILTER('data table',[Test]>=0.25&&[Test]<0.5 && 'data table'[Model]=MAX('Table'[Model])))
var _05_075 = CALCULATE(DISTINCTCOUNT('data table'[Model]),FILTER('data table',[Test]>=0.5&&[Test]<0.75 && 'data table'[Model]=MAX('Table'[Model])))
var _075 = CALCULATE(DISTINCTCOUNT('data table'[Model]),FILTER('data table',[Test]>=0.75&& 'data table'[Model]=MAX('Table'[Model])))
return
SWITCH(
TRUE(),
MAX('Table'[Value]) = "<0.25",_025,
MAX('Table'[Value]) = "0.25-0.5",_025_05,
MAX('Table'[Value]) = "0.5-0.75",_05_075,
MAX('Table'[Value]) = ">0.75",_075,
MAX('Table'[Value]) = "Total",_025+_025_05+_05_075+_075)`````` The SWITCH function means that when the current column is equal to a certain value, output the corresponding value.

For example, when Table[Value] = “<0.25”, then output the value conforming to <0.25.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

24 REPLIES 24  Super User

I don't think this will help.

Try looking at Segmentation as suggested by @amitchandak

There is a link for this as well for an existing thread on this:

https://community.powerbi.com/t5/Desktop/Dynamic-segmentation-of-customers-based-on-the-last-few-yea...

Thanks,

Pragati

My Blog: Data Vibe

If this helps, Appreciate a KUDOS!

Proud to be a Super User!  Super User

You cannot create a group on measure

Unfortunately, You need to create 4 different measures each for particular range

then put those measures in column and you will get your results.

Proud to be a Super User!  Super User

What you can try is created a calculated column which defines your categories as <0.25 , 0.25-0.50 , 0.50-0.75, >0.75 based on your TEST measure.

Then try using this column in a matrix visual under COLUMN section along with your Name column under ROW section.

Thanks,

Pragati

My Blog: Data Vibe

If this helps, Appreciate a KUDOS!

Proud to be a Super User!  Solution Sage

Dear pragati , I appreciate your response

But if I created a calculated column my values will not change when I change date selection from my date slicer  