Solved: Create custom column in a table

vnqt · ‎09-12-2022

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	Host 3	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 4	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 5	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 6	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 7	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 8	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 9	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 10	C4580_Ec7502_1_32
APTTYV1V	CL_MU_FGT	Host 11	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 12	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 13	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 14	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 15	D4000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 16	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 17	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 18	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 19	R2000_c7502_1_32
APTTYV1V	CL_MU_FGT	Host 20	R2000_c7502_1_32
APTTYV1N	CL_MU_F30	Host 21	C4580_Ec7502_1_32
APTTYV1N	CL_MU_F30	Host 22	A2000_c7502_1_32
APTTYV1N	CL_MU_F30	Host 23	A2000_c7502_1_32
APTTYV1N	CL_MU_F30	Host 24	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 25	C4580_Ec7502_1_32
APTTYV1BV	CL_RG_AZE	Host 26	C4580_Ec7502_1_32
APTTYV1BV	CL_RG_AZE	Host 27	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 28	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 29	A2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 30	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 31	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 32	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 33	R2000_c7502_1_32
APTTYV1BV	CL_RG_AZE	Host 34	E2030_c7502_1_32

I would like to create the table/Matrix as below:

category	Sub Category	Number of Model	Nb VM	VM Windows	VM Linux	VM other	Nb Host	Model 1	Nb Host	Model 2	Nb Host	Model 3	Nb Host	Model 4	Nb Host
APTTYV1V	CL_MU_FGT	3	720	0	710	10	20	C4580_Ec7502_1_32	10	D4000_c7502_1_32	5	R2000_c7502_1_32	5
APTTYV1N	CL_MU_F30	2	200	20	170	10	4	C4580_Ec7502_1_32	1	A2000_c7502_1_32	3
APTTYV1BV	CL_RG_AZE	4	200	20	170	10	10	C4580_Ec7502_1_32	2	A2000_c7502_1_32	3	R2000_c7502_1_32	4	E2030_c7502_1_32	1

1. The columns "Model 1, 2, 3 ,4 " is the list of avalaible models of each "Sub-category"

2. "Nb Host" is total of Host of each Model

Could you please advise?

Thank you in advance.

Anonymous · ‎09-13-2022

Hi @vnqt ,

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.

View solution in original post

Anonymous · ‎09-13-2022

Hi @vnqt ,

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.

vnqt · ‎09-13-2022

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

vnqt · ‎09-13-2022

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

vnqt · ‎09-12-2022

Hi @Greg_Deckler

I guess I wasn't clear enough.

The columns I would like to add is in blue and orange .

- The available model listed in the columns Model 1, Model 2, Model 3, Model 4 . Normally a Sub-category has max 4 model

- The number of host of each Sub-category by Model

Greg_Deckler · ‎09-12-2022

@vnqt Try:

Number of Models Measure = 
  COUNTROWS(DISTINCT(SELECTCOLUMNS('Table',"category",[category],"subcategory",[Sub Category],"model",[Model])))


Nb VM Measure = COUNTROWS(DISTINCT('Table'))

Follow on LinkedIn
@ 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!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Create custom column in a table

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

How to Get Your Question Answered Quickly

Huge last-minute discounts for FabCon Vienna from September 15-18, 2025

Create custom column in a table

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

How to Get Your Question Answered Quickly