Solved: Re: DAX - Page 2

Sujit_Thakur · ‎08-04-2020

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

v-zhenbw-msft · ‎08-06-2020

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.

View solution in original post

Pragati11 · ‎08-04-2020

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

Best Regards,

Pragati Jain

LinkedIn | Twitter | Blog | YouTube

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

FarhanAhmed · ‎08-04-2020

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 · ‎08-04-2020

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

Best Regards,

Pragati Jain

LinkedIn | Twitter | Blog | YouTube

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Sujit_Thakur · ‎08-04-2020

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

DAX

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly