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

Need Help

v-zhenbw-msft

6 years ago

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.

DAX.pbix27 KB

Sujit_Thakur

Solution Sage

6 years ago

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

v-zhenbw-msft

Community Support

6 years ago

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