Forum Discussion

sovereignauto's avatar
sovereignauto
Helper III
5 years ago
Solved

Detailed summary Table

Good Morning,

 

So once again after some help! 

I have the attached which is an example as orginal has 2 years of data and many more rows and columns (about 10k rows per year for claims and 150k per year for notes) that i need to try to work out how to do the following.. 

" I need to see for each day of the year how many files each handler had open and the % of these where the last note was more than 3 days ago" 

So i have 1 table which contains all file references with the date opend and date closed with handler name (as part of a bigger table),

I then have a notes table with Date of note and reference (as part of a bigger table) 

So im thinking i need to somehow create a date table which summarises by handler how many open files they had and of them open files how many werent contacted "in the notes table" in the last 3 days and then divide one against the other.



EDIT:  DropBox File 

Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    5 years ago

    Hi sovereignauto ,

     

    Create a calendar table then add the following measure to your model:

    Number claims open =
COUNTROWS (
    FILTER (
        Claims,
        Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
            && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
    )
)
    PercentageOfNotes =
VAR tempTable =
    SELECTCOLUMNS (
        FILTER (
            Claims,
            Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
                && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
        ),
        "Claim", Claims[Ref]
    )
VAR Notes_Values =
    SUMMARIZE (
        FILTER (
            'Notes',
            Notes[Date] <= MAX ( 'Calendar'[Date] )
                && Notes[Date]
                    >= MAX ( 'Calendar'[Date] ) - 2
                && Notes[Ref] IN tempTable
        ),
        Notes[Ref]
    )
RETURN
    DIVIDE ( COUNTROWS ( Notes_Values ), [Number claims open] )

     

    See result below and in attach PBIX file.

     

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi sovereignauto ,

     

    Create a calendar table then add the following measure to your model:

    Number claims open =
COUNTROWS (
    FILTER (
        Claims,
        Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
            && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
    )
)
    PercentageOfNotes =
VAR tempTable =
    SELECTCOLUMNS (
        FILTER (
            Claims,
            Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
                && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
        ),
        "Claim", Claims[Ref]
    )
VAR Notes_Values =
    SUMMARIZE (
        FILTER (
            'Notes',
            Notes[Date] <= MAX ( 'Calendar'[Date] )
                && Notes[Date]
                    >= MAX ( 'Calendar'[Date] ) - 2
                && Notes[Ref] IN tempTable
        ),
        Notes[Ref]
    )
RETURN
    DIVIDE ( COUNTROWS ( Notes_Values ), [Number claims open] )

     

    See result below and in attach PBIX file.

     

    • sovereignauto's avatar
      sovereignauto
      Helper III
      5 years ago

      MFelix thank you so much it is a great start! 

      Do you know how i would be able to "group by" Handler as well as date? so for each date how many files each handler had?

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi sovereignauto ,

         

        Not sure if I understand what you want to but in the example I send out you just need to add the Handler to the Column series:

         

        In the way the measures are calculated you can use any column on your tables to give further detail.