Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DAX

08-04-2020
02:21 AM

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.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
12:35 AM

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

BTW, pbix as attached.

24 REPLIES 24

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2020
03:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
12:35 AM

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

BTW, pbix as attached.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
01:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
02:41 AM

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

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2020
01:37 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2020
01:39 AM

Yes @Anonymous ,

its SUMX(Table,'Table'[HP]) / SUMX(Table,'Table'[OP])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2020
01:40 AM

@Anonymous so that everytime i slice date range , HP / OP is recalculated and values in output table changes

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2020
12:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2020
01:09 AM

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 .

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
11:53 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2020
01:59 AM

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

** helps**, then please consider

BTW, pbix as attached.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
06:53 PM

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

** helps**, then please consider

BTW, pbix as attached.

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
05:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
02:53 AM

@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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
05:18 AM

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

Microsoft Hates Greg's Quick Measures

Check out my latest book!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
02:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
02:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2020
02:41 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content