Forum Discussion

Sujit_Thakur's avatar
Sujit_Thakur
Solution Sage
6 years ago
Solved

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's avatar
    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.

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

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

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.