cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sujit_Thakur
Solution Sage
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

@nickyvv  

 

Regards ,

Sujit 

1 ACCEPTED SOLUTION

Hi @Sujit_Thakur ,

 

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]))

 

D1.jpg

 

2. Create a new table.

 

Table = CROSSJOIN(VALUES('index table'[Model]),{"<0.25","0.25-0.5","0.5-0.75",">0.75","Total"})

 

D2.jpg

 

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)

 

D3.jpg

 

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)

 

D4.jpg

 

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.

View solution in original post

24 REPLIES 24

Hi @Sujit_Thakur ,

 

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
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a 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.

 

 







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

Proud to be a Super User!




Pragati11
Super User
Super User

Hi @Sujit_Thakur ,

 

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
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!