Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@nickyvv  

 

Regards ,

Sujit 

1 ACCEPTED 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]))

 

D1.jpg

 

2. Create a new table.

 

Table = CROSSJOIN(VALUES('index table'[Model]),{"<0.25","0.25-0.5","0.5-0.75",">0.75","Total"})

 

D2.jpg

 

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)

 

D3.jpg

 

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)

 

D4.jpg

 

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

24 REPLIES 24
Sujit_Thakur
Solution Sage
Solution Sage

Dear  @v-zhenbw-msft  ,

following is my data 

 1)First table is index table

Id NameModel
1AAlpha
2BAlpha
3CBravo
4DBravo
5ECharlie

2)Second is data table 

DateName HPOP
7/1/2020A2322
7/2/2020A2019
7/3/2020A2029
7/4/2020A2240
7/5/2020A21100
7/1/2020B2250
7/2/2020B2150
7/3/2020B23100
7/4/2020B2223
7/5/2020B2017
7/1/2020C2320
7/2/2020C2117
7/3/2020C2215
7/4/2020C2114
7/5/2020C2312
7/1/2020D21100
7/2/2020D2250
7/3/2020D2150
7/4/2020D23100
7/5/2020D2223
7/1/2020E2017
7/2/2020E2320
7/3/2020E2317
7/4/2020E2350
7/5/2020E23100

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]))

 

D1.jpg

 

2. Create a new table.

 

Table = CROSSJOIN(VALUES('index table'[Model]),{"<0.25","0.25-0.5","0.5-0.75",">0.75","Total"})

 

D2.jpg

 

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)

 

D3.jpg

 

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)

 

D4.jpg

 

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.

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?

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 

Anonymous
Not applicable

Hi @Sujit_Thakur 

 

Add this table to your model. I have named this HP-OP-Bounds.

HP-OP Bounds.PNG

 

After that create two calculated columns(HP/OP and Bin) as shown below in your table. I have named this table 'HP-OP'.

 

HP-OP.PNG

 

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.

Matrix.PNG

 

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 .

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

 

DateName HPOP
7/1/2020A2322
7/2/2020A2019
7/3/2020A2029
7/4/2020A2240
7/5/2020A21100
7/1/2020B2250
7/2/2020B2150
7/3/2020B23100
7/4/2020B2223
7/5/2020B2017
7/1/2020C2320
7/2/2020C2117
7/3/2020C2215
7/4/2020C2114
7/5/2020C2312

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"})

 

DAX3.jpg

 

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]))))

 

DAX4.jpgDAX5.jpg

 

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.

v-zhenbw-msft
Community Support
Community Support

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"})

 

DAX1.jpg

 

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]))))

 

DAX2.jpg

 

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.

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.

Sujit_Thakur
Solution Sage
Solution Sage

Dear @Greg_Deckler 

 

Please spread  some light on above question 🙏

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Arklur
Resolver II
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.

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

 

Best Regards,

Pragati Jain


MVP logo


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!!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.