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.
Dear @v-zhenbw-msft ,
following is my data
1)First table is index table
Id | Name | Model |
1 | A | Alpha |
2 | B | Alpha |
3 | C | Bravo |
4 | D | Bravo |
5 | E | Charlie |
2)Second is data table
Date | Name | HP | OP |
7/1/2020 | A | 23 | 22 |
7/2/2020 | A | 20 | 19 |
7/3/2020 | A | 20 | 29 |
7/4/2020 | A | 22 | 40 |
7/5/2020 | A | 21 | 100 |
7/1/2020 | B | 22 | 50 |
7/2/2020 | B | 21 | 50 |
7/3/2020 | B | 23 | 100 |
7/4/2020 | B | 22 | 23 |
7/5/2020 | B | 20 | 17 |
7/1/2020 | C | 23 | 20 |
7/2/2020 | C | 21 | 17 |
7/3/2020 | C | 22 | 15 |
7/4/2020 | C | 21 | 14 |
7/5/2020 | C | 23 | 12 |
7/1/2020 | D | 21 | 100 |
7/2/2020 | D | 22 | 50 |
7/3/2020 | D | 21 | 50 |
7/4/2020 | D | 23 | 100 |
7/5/2020 | D | 22 | 23 |
7/1/2020 | E | 20 | 17 |
7/2/2020 | E | 23 | 20 |
7/3/2020 | E | 23 | 17 |
7/4/2020 | E | 23 | 50 |
7/5/2020 | E | 23 | 100 |
Now what i want as an output is
Model Total Count <0.25 0.25-0.50 0.50-0.75 >0.75
Alpha 2 2
Bravo 2 1 1
Charlie 1 1
please also explain your code of switch function i didnt understood
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.
Thanks @v-zhenbw-msft
You have helped me a lot .
One last doubt ,
What if my data grown , does it will hamper the Visualization calculation speed ?
Like now I have only 5 names , what if tomorrow I get data for 5000 names then the crossjoin table would size almost 25000 rows ? I just wanted to ask will this hamper my models efficiency ? About how much space it will take ?
Please help
Hi @Sujit_Thakur ,
Sorry for that we have not tested how much space it takes up when it groups.
It has little effect on the speed of visualization, if your data is not very very large, but remember the more data that a visual needs to display, the slower that visual is to load.
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.
@Sujit_Thakur How do you intend too use date slicer?
I guess the HP/OP formulae become SUM(HP)/SUM(OP) for a selected date range right?
Yes @Anonymous ,
its SUMX(Table,'Table'[HP]) / SUMX(Table,'Table'[OP])
@Anonymous so that everytime i slice date range , HP / OP is recalculated and values in output table changes
Add this table to your model. I have named this HP-OP-Bounds.
After that create two calculated columns(HP/OP and Bin) as shown below in your table. I have named this table 'HP-OP'.
The formulaes for calculated columns are shown below.
HP/OP = DIVIDE('HP-OP'[HP], 'HP-OP'[OP], 0)
Bin =
VAR X = 'HP-OP'[HP/OP]
VAR Filtered =
FILTER (
'HP-OP-Bounds',
VAR Y = 'HP-OP-Bounds'[Upper Bound] RETURN 'HP-OP-Bounds'[Lower Bound] <= X
&& IF ( ISBLANK ( y ), TRUE (), Y > X )
)
RETURN
MAXX ( Filtered, 'HP-OP-Bounds'[Bin] )
Now use matrix visual as shown below.
Hope this helps.
Appreciate with kudos.
Please mark it as solution if this resolves your problem.
Thanks
Dear friend @Anonymous ,
Can I create this same using measure , as I want to later use date slicer , using calculated columns will not help me when I want HP/OP to be recalculated every time I change date range .
You seriously brought me much closer to ans , please answer how can I achieve by using HP/OP as a calculated measure , because as you can see I have date column too of which I will add date slicer .
Dear @v-zhenbw-msft @Greg_Deckler and all ,
following is pasted data sample for question , please note the measure" Test" is nothing but divide(HP,OP).
I hope after this sample data @v-zhenbw-msft you can help me with your previously discussed technique
Date | Name | HP | OP |
7/1/2020 | A | 23 | 22 |
7/2/2020 | A | 20 | 19 |
7/3/2020 | A | 20 | 29 |
7/4/2020 | A | 22 | 40 |
7/5/2020 | A | 21 | 100 |
7/1/2020 | B | 22 | 50 |
7/2/2020 | B | 21 | 50 |
7/3/2020 | B | 23 | 100 |
7/4/2020 | B | 22 | 23 |
7/5/2020 | B | 20 | 17 |
7/1/2020 | C | 23 | 20 |
7/2/2020 | C | 21 | 17 |
7/3/2020 | C | 22 | 15 |
7/4/2020 | C | 21 | 14 |
7/5/2020 | C | 23 | 12 |
Hi @Sujit_Thakur ,
We can create a measure to meet your requirement.
Before that we need to create a new table.
Table = CROSSJOIN(VALUES('Data'[Name]),{"<0.25","0.25-0.5","0.5-0.75",">0.75"})
Then create a measure like this,
Measure =
SWITCH(
TRUE(),
MAX('Table'[Value]) = "<0.25",CALCULATE(COUNT('Data'[Name]),FILTER('Data',[Test]<0.25 && 'Data'[Name]=MAX('Table'[Name]))),
MAX('Table'[Value]) = "0.25-0.5",CALCULATE(COUNT('Data'[Name]),FILTER('Data',[Test]>=0.25&&[Test]<0.5 && 'Data'[Name]=MAX('Table'[Name]))),
MAX('Table'[Value]) = "0.5-0.75",CALCULATE(COUNT('Data'[Name]),FILTER('Data',[Test]>=0.5&&[Test]<0.75 && 'Data'[Name]=MAX('Table'[Name]))),
MAX('Table'[Value]) = ">0.75",CALCULATE(COUNT('Data'[Name]),FILTER('Data',[Test]>=0.75 && 'Data'[Name]=MAX('Table'[Name]))))
If you have any questions, please kindly ask here and we will try to resolve it.
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 ,
We create a sample. We can use a matrix table and a measure to meet your requirement.
1. Create a new table.
Table 2 = CROSSJOIN(VALUES('Table'[Name]),{"<0.25","0.25-0.5"})
2. Then we can create a matrix table based on the new table, create a measure and put it in Values.
Measure 2 =
SWITCH(
TRUE(),
MAX('Table 2'[Value]) = "<0.25",CALCULATE(COUNT('Table'[Name]),FILTER('Table',[Measure]<0.25 && 'Table'[Name]=MAX('Table 2'[Name]))),
MAX('Table 2'[Value]) = "0.25-0.5",CALCULATE(COUNT('Table'[Name]),FILTER('Table',[Measure]>=0.25&&[Measure]<0.5 && 'Table'[Name]=MAX('Table 2'[Name]))))
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
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.
@Sujit_Thakur If the segments are fixed i.e. (<0.25 0.25-0.50 0.50-0.75 >0.75).
You can use the parameter table pattern.
@Sujit_Thakur , see if segmentation can help
SEGMENTATION
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
@Sujit_Thakur - Since you mentioned me I'll chime in. Unless I missed it, I don't have a good sense of your source data. I see the expected output. Can you post a sample of what your source data looks like or point me to where it is posted? Thanks.
@Sujit_Thakur wrote:Which returns a table so I was thinking if is it possible to get my expected result using evaluate
No, EVALUATE is not avaialbe within PBI, at least not in this sense. The only place it could be used is to import data from SSAS, for example.
Dear @Arklur ,
Is there any way of getting an output as an table using DAX ?
I have dax studio installed on my system , I read somewhere that I can use evaluate using DAX studio
Hi @Sujit_Thakur ,
EVALUATE is not available as a DAX function in POwer BI.
When you tallk about EVALUATE in DAX Studio tool, it just serves as a starting query variable to write a dax expresion within it for dax studio tool to execute it.
In DAX studio tool, a DAX expression is identified within EVALUATE clause. So, EVALUATE is just a clause used to write your dax expression in this tool. It's just an statement needed to execute a query within this tool.
You can read in detail here: https://dax.guide/st/evaluate/
Thanks,
Pragati
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 |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |