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  Solution Sage

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  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.  Solution Sage

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 ?  Community Support

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. Anonymous
Not applicable

@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?  Solution Sage

Yes @Anonymous  ,
its  SUMX(Table,'Table'[HP]) / SUMX(Table,'Table'[OP])  Solution Sage

@Anonymous  so that everytime i slice date range , HP / OP is recalculated and values in output table changes Anonymous
Not applicable 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.

Thanks  Solution Sage

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 .  Solution Sage

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  Community Support

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.  Community Support

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.

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. Anonymous
Not applicable

@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.  Super User

@Sujit_Thakur , see if segmentation can help

SEGMENTATION

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Solution Sage

Dear @Greg_Deckler  Super User

@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.

Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!    Resolver II

@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.  Solution Sage

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  Super User

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

My Blog: Data Vibe

If this helps, Appreciate a KUDOS!

Proud to be a Super User!  Solution Sage

@Pragati11  can calculate table be used here in any way?  