Forum Discussion
Create custom column in a table
Hello, I have the data as below : category Sub Category Host Model APTTYV1V CL_MU_FGT Host 1 C4580_Ec7502_1_32 APTTYV1V CL_MU_FGT Host 2 C4580_Ec7502_1_32 APTTYV1V CL_MU_FGT...
Hi Anonymous ,
Please create below measure, and add it to table visual.
Number of Model = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category ) RETURN DISTINCTCOUNT ( [Model] )Model 1 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category ) RETURN MAXX ( tmp, [Model] )Nb Host For Model 1 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR cur_model = [Model 1] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category && 'Table'[Model] = cur_model ) RETURN COUNTROWS ( tmp )Model 2 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR pre_model1 = [Model 1] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category && 'Table'[Model] <> pre_model1 ) RETURN MAXX ( tmp, [Model] )Nb Host For Model 2 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR cur_model = [Model 2] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category && 'Table'[Model] = cur_model ) RETURN COUNTROWS ( tmp )Model 3 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR pre_model1 = [Model 1] VAR pre_model2 = [Model 2] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category && 'Table'[Model] <> pre_model1 && 'Table'[Model] <> pre_model2 ) RETURN IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )Nb Host For Model 3 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR cur_model = [Model 3] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category && 'Table'[Model] = cur_model ) RETURN IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )Model 4 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR pre_model1 = [Model 1] VAR pre_model2 = [Model 2] VAR pre_model3 = [Model 3] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category && 'Table'[Model] <> pre_model1 && 'Table'[Model] <> pre_model2 && 'Table'[Model] <> pre_model3 ) RETURN IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )Nb Host For Model 4 = VAR cur_category = SELECTEDVALUE ( 'Table'[category] ) VAR cur_sub_category = SELECTEDVALUE ( 'Table'[Sub Category] ) VAR cur_model = [Model 4] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[category] = cur_category && 'Table'[Sub Category] = cur_sub_category && 'Table'[Model] = cur_model ) RETURN IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Please create below measure, and add it to table visual.
Number of Model =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
)
RETURN
DISTINCTCOUNT ( [Model] )
Model 1 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
)
RETURN
MAXX ( tmp, [Model] )
Nb Host For Model 1 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 1]
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
&& 'Table'[Model] = cur_model
)
RETURN
COUNTROWS ( tmp )
Model 2 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
&& 'Table'[Model] <> pre_model1
)
RETURN
MAXX ( tmp, [Model] )
Nb Host For Model 2 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 2]
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
&& 'Table'[Model] = cur_model
)
RETURN
COUNTROWS ( tmp )
Model 3 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR pre_model2 = [Model 2]
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
&& 'Table'[Model] <> pre_model1
&& 'Table'[Model] <> pre_model2
)
RETURN
IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )
Nb Host For Model 3 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 3]
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
&& 'Table'[Model] = cur_model
)
RETURN
IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )
Model 4 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR pre_model1 = [Model 1]
VAR pre_model2 = [Model 2]
VAR pre_model3 = [Model 3]
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
&& 'Table'[Model] <> pre_model1
&& 'Table'[Model] <> pre_model2
&& 'Table'[Model] <> pre_model3
)
RETURN
IF ( ISEMPTY ( tmp ), BLANK (), MAXX ( tmp, [Model] ) )
Nb Host For Model 4 =
VAR cur_category =
SELECTEDVALUE ( 'Table'[category] )
VAR cur_sub_category =
SELECTEDVALUE ( 'Table'[Sub Category] )
VAR cur_model = [Model 4]
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[category] = cur_category
&& 'Table'[Sub Category] = cur_sub_category
&& 'Table'[Model] = cur_model
)
RETURN
IF ( ISEMPTY ( tmp ), BLANK (), COUNTROWS ( tmp ) )
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Thank you for your solution, it works well until I filter by Catagory. This slicer uses Catagory column in another table which has the relation with this table.
When I apply the filter, the Model is empty but all other columns have value:
How could I replace the column Category of other table in all the measures that we've just created?
MAny thanks in advance.
Anonymous
Could you please advise how to add the total of HOST of each model ?
I have another column name CPO as below, could you help to calculate the sum of CPO of each Model ?
categorySub CategoryHostModelcpO
APTTYV1V CL_MU_FGT Host 1 C4580_Ec7502_1_32 2 APTTYV1V CL_MU_FGT Host 2 C4580_Ec7502_1_32 3 APTTYV1V CL_MU_FGT Host 3 C4580_Ec7502_1_32 4 APTTYV1V CL_MU_FGT Host 4 C4580_Ec7502_1_32 1 APTTYV1V CL_MU_FGT Host 5 C4580_Ec7502_1_32 2 APTTYV1V CL_MU_FGT Host 6 C4580_Ec7502_1_32 33 APTTYV1V CL_MU_FGT Host 7 C4580_Ec7502_1_32 2 APTTYV1V CL_MU_FGT Host 8 C4580_Ec7502_1_32 6 APTTYV1V CL_MU_FGT Host 9 C4580_Ec7502_1_32 8 APTTYV1V CL_MU_FGT Host 10 C4580_Ec7502_1_32 12 APTTYV1V CL_MU_FGT Host 11 D4000_c7502_1_32 9 APTTYV1V CL_MU_FGT Host 12 D4000_c7502_1_32 8 APTTYV1V CL_MU_FGT Host 13 D4000_c7502_1_32 14 APTTYV1V CL_MU_FGT Host 14 D4000_c7502_1_32 2 APTTYV1V CL_MU_FGT Host 15 D4000_c7502_1_32 3 APTTYV1V CL_MU_FGT Host 16 R2000_c7502_1_32 4 APTTYV1V CL_MU_FGT Host 17 R2000_c7502_1_32 22 APTTYV1V CL_MU_FGT Host 18 R2000_c7502_1_32 24 APTTYV1V CL_MU_FGT Host 19 R2000_c7502_1_32 33 APTTYV1V CL_MU_FGT Host 20 R2000_c7502_1_32 2 APTTYV1N CL_MU_F30 Host 21 C4580_Ec7502_1_32 12 APTTYV1N CL_MU_F30 Host 22 A2000_c7502_1_32 12 APTTYV1N CL_MU_F30 Host 23 A2000_c7502_1_32 12 APTTYV1N CL_MU_F30 Host 24 A2000_c7502_1_32 8 APTTYV1BV CL_RG_AZE Host 25 C4580_Ec7502_1_32 12 APTTYV1BV CL_RG_AZE Host 26 C4580_Ec7502_1_32 9 APTTYV1BV CL_RG_AZE Host 27 A2000_c7502_1_32 8 APTTYV1BV CL_RG_AZE Host 28 A2000_c7502_1_32 14 APTTYV1BV CL_RG_AZE Host 29 A2000_c7502_1_32 2 APTTYV1BV CL_RG_AZE Host 30 R2000_c7502_1_32 3 APTTYV1BV CL_RG_AZE Host 31 R2000_c7502_1_32 4 APTTYV1BV CL_RG_AZE Host 32 R2000_c7502_1_32 22 APTTYV1BV CL_RG_AZE Host 33 R2000_c7502_1_32 24 APTTYV1BV CL_RG_AZE Host 34 E2030_c7502_1_32 33 Thank you in advance.