Forum Discussion

hcze's avatar
hcze
Icon for Helper II rankHelper II
1 year ago
Solved

Calculated measure as hierarchy

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

 

ClientCountryPolicyInception DateCancelation DateClaim NoClaim Date
ABCUSP115/01/2024 C013/02/2024
ABCUKP217/01/20242/02/2024  
ABCUKP320/02/2024 C035/05/2024
ABCUKP431/03/2024 C04 
ABCUKP531/03/2024 C05 
ABCUSP610/03/2024 C06 
ABCUSP71/05/2024 C0720/06/2024
ABCUKP82/05/2024 C08 
ABCUSP93/05/2024 C09 

 


and here is my expected result

 

   JanFebMarAprMayJune
Active Policy count  225080
 ABC 225080
  US112040
  UK113040
         
Claims Count  010011
 ABC 010011
  US010001
  UK000010

 

Thank you!

DAX
Hierarchy
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Icon for Solution Sage rankSolution Sage
    1 year ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.