Forum Discussion

chamomile16's avatar
chamomile16
Frequent Visitor
2 years ago

Create a measure to include latest data across category rather than within category in a Treemap

I have the below data:

ReportingDateValuationTypeClosingValuation
3/30/2023Recent acquisition cost100
3/30/2023External Manager229
3/30/2023Independent Valuation300
3/30/2023NULL400
3/30/2023Internal Valuation90
3/30/2023Recent transaction price780
6/30/2020External Manager50
6/30/2020Independent Valuation50
6/30/2020NULL56
6/30/2020Desktop Review606
6/30/2020Internal Valuation60

 

I created a measure with the DAX code like below. I want to show the sum of latest valuation (valuation with latest reporting date) by default (not applying any filters).

 

Total Latest Valuation = 
  
VAR SelectedDate = SELECTEDVALUE ( 'I&RR Valuation'[Reporting Date] )  

VAR EffectiveDate =  
    IF (  
        ISBLANK(SelectedDate),  
        IF (  
            HASONEVALUE('I&RR Valuation'[Reporting Date]),  
            VALUES('I&RR Valuation'[Reporting Date]),  
            CALCULATE (  
                MAX('I&RR Valuation'[Reporting Date]),  
                ALLSELECTED('I&RR Valuation')  
            )  
        ),  
        SelectedDate  
    )  
  

RETURN  
    CALCULATE (  
        SUM('I&RR Valuation'[Closing Valuation]),  
        'I&RR Valuation'[Reporting Date] = EffectiveDate
    )

 

 

The code works fine for my other visuals, but when I use it as the Values of my treemap, and use ValuationType as Category, it isn't working as expected:

Here, Desktop Review should not be included, as the latest reporting date for records with this Valuation Type is 2020-06-30. 2020-06-30 is not the latest reporting date in the table.

 

What I want is only records with reporting date = 2023-03-30 as this is the latest reporting date in the table. Expected output:

I can get this expected output when selecting 2023-03-30 in the ReportingDate filter. It doesn't show this expected result when no filters are applied.

 

Looks like the issue is that the treemap determines the latest valuation within each category rather than across categories, so it includes the sum of latest valuation per valuation type.

Is there a way to have the treemap show the expected result, by modifying DAX code, ignoring category when determing latest valuation or any other methods?

 

I hope my description of the problem makes sense. Thanks very much for any advices/hints!

Need Help

6 Replies

  • chamomile16's avatar
    chamomile16
    Frequent Visitor
    2 years ago

    Note: I forgot to mention in the original post that I enabled personalized visualization for my report.

    Therefore, only modifying DAX to include ValuationType may not be the best solution, as users may select other columns as Category when personalizing the visual. 

     

    This is also the reason why this problem gives me so much headache......really need some pro's help please! Thank you!!

      • chamomile16's avatar
        chamomile16
        Frequent Visitor
        2 years ago

        Hi Ashish, I really appreciate your input!

        I tweaked and applied it to my report, and it works great when it's in default state.

         

        When I apply other filters, for example, Investment, I hope it to show the latest valuation for that investment.

        E.g., when I select Apple in my filter, the expected result is to show the latest valuation for Apple (on 2020-06-30), with Valuation Type External Manager and Desktop Review.

        ReportingdateInvestmentValuation TypeClosing Valuation
        3/30/2023ABC propertyRecent acquisition cost100
        3/30/2023BE propertyExternal Manager229
        3/30/2023 Independent Valuation300
        3/30/2023 NULL400
        3/30/2023 Internal Valuation90
        3/30/2023 Recent transaction price780
        6/30/2020AppleExternal Manager50
        6/30/2020BananaIndependent Valuation50
        6/30/2020 NULL56
        6/30/2020AppleDesktop Review606
        6/30/2020 Internal Valuation

        60   

         

        But what it actually shows is Blank(), as 2020-06-30 is not the latest reporting date for whole table.

         

        In my previous code, I used the highlighted part below to have this function work for my other visuals, but unfortunately I think this is exactly what caused my original problem in the treemap (the Desktop Review that shouldn't be there in default state) 

         

        VAR SelectedDate = SELECTEDVALUE ( 'I&RR Valuation'[Reporting Date] )

        VAR EffectiveDate =
        IF (
        ISBLANK(SelectedDate),
        IF (
        HASONEVALUE('I&RR Valuation'[Reporting Date]),
        VALUES('I&RR Valuation'[Reporting Date]),
        CALCULATE (
        MAX('I&RR Valuation'[Reporting Date]),
        ALLSELECTED('I&RR Valuation')
        )
        ),
        SelectedDate
        )

         

        Is there possibly a way to get the expected result when applying other filters, so it doesn't only show the latest reporting date of the whole table? I understand that this may be hard, and thanks again for helping!