topic Re: Calculated measure as hierarchy in DAX Commands and Tips

Calculated measure as hierarchy

hcze — Fri, 27 Sep 2024 13:07:42 GMT

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!

Re: Calculated measure as hierarchy

Selva-Salimi — Fri, 27 Sep 2024 13:24:39 GMT

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.

Re: Calculated measure as hierarchy

Anonymous — Mon, 30 Sep 2024 03:51:02 GMT

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.