Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]))
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.
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:
Thanks,
Pragati
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!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |