Calculated measure as hierarchy

[hcze]

Hi,

I have issue trying to have matrix to show calculated measure as row header/hierarchy. Any helps will be appreciated.

Here is my test data and also separate standard calendar table that I dont add here

Client	Country	Policy	Inception Date	Cancelation Date	Claim No	Claim Date
ABC	US	P1	15/01/2024		C01	3/02/2024
ABC	UK	P2	17/01/2024	2/02/2024
ABC	UK	P3	20/02/2024		C03	5/05/2024
ABC	UK	P4	31/03/2024		C04
ABC	UK	P5	31/03/2024		C05
ABC	US	P6	10/03/2024		C06
ABC	US	P7	1/05/2024		C07	20/06/2024
ABC	UK	P8	2/05/2024		C08
ABC	US	P9	3/05/2024		C09

and here is my expected result

			Jan	Feb	Mar	Apr	May	June
Active Policy count			2	2	5	0	8	0
	ABC		2	2	5	0	8	0
		US	1	1	2	0	4	0
		UK	1	1	3	0	4	0
Claims Count			0	1	0	0	1	1
	ABC		0	1	0	0	1	1
		US	0	1	0	0	0	1
		UK	0	0	0	0	1	0

Thank you!

[Anonymous · Accepted solution]

Hi @hcze ,

Here I create a sample to have a test.

Data Table:

DimTables:

DimCount = 
DATATABLE(
    "Count Period",STRING,
    "Order",INTEGER,
    {
        {"Active Policy Count",1},
        {"Claims Count",2}
    })

DimDate = ADDCOLUMNS(CALENDAR(EOMONTH(MIN('Table'[Inception Date]),-1)+1,EOMONTH(MAX('Table'[Claim Date]),0)),"Year",YEAR([Date]),"MonthNo",MONTH([Date]),"Month",FORMAT([Date],"MMM"))

Measure:

Measure = 
VAR _HAVEDATA =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Policy] ),
        FILTER (
            ALLEXCEPT('Table','Table'[Client]),
            'Table'[Inception Date] >= MIN ( DimDate[Date] )
                && 'Table'[Inception Date] <= MAX ( DimDate[Date] )
        )
    ) + 0
VAR _ActiveCount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Policy] ),
        FILTER (
            'Table',
            'Table'[Inception Date] <= MAX ( DimDate[Date] )
                && OR (
                    'Table'[Cancelation Date] = BLANK (),
                    'Table'[Cancelation Date] > MAX ( DimDate[Date] )
                )
        )
    ) + 0
VAR _ClaimsCount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Policy] ),
        FILTER (
            'Table',
            'Table'[Claim Date] >= MIN ( DimDate[Date] )
                && 'Table'[Claim Date] <= MAX ( DimDate[Date] )
        )
    ) + 0
RETURN
    SWITCH (
        MAX ( DimCount[Count Period] ),
        "Active Policy Count", IF ( _HAVEDATA = 0, 0, _ActiveCount ),
        "Claims Count", _ClaimsCount
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Selva-Salimi]

Hi @hcze 

you should create another table add these two phrase "Active Policy count" and "Claim counts" in that with Id's 1 and 2 to manage. add the column of phrase in your visual matrix and then client from your table and then write a measure as follows:

if ( selectedvalue ([ID] =1 , [measure to calculate active policy count] , if (selectedvalue ([ID]=2 , [measure to calculate claim counts], 0))

add this measure to the value of your matrix.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.