Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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?
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |