Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need to display just 9 months of data for each year for 2018, 2019 and 2020 to compare frequencey for top 5 causes.
.i.e., i need to display data in a chart for date starting from 01/01/2018 to 09/30/2020.
Please find the ss of the data to be displayed in this format.
how to achive this?
what would be the right visualization chart to display the data in the below attached format?
Hello @v-shex-msft ,
Can you please help me achieve the ask. Please follow the SS first
I need to display the values under MININCURRED for valuation Date 07/15/2020 beside valuation date 07/15/2021. I need to elimiate the values displayed under valuation date 07/15/2021 under MININCURRED i.e, for valuation date 07/15/2021, MAXINCURRED is 82,039 and MININCURRED should be 879 for the cause Stain or Injury by Lifting and following the same for the rest.
Let me tell you the Dax queires used. :
MAXVAL : MAXVAL = CALCULATE([Total Incurred_NEW],
FILTER('clm ValuationData',MAX('clm ValuationData'[ValuationDate])),
(DATESBETWEEN('clm Claims'[LossDate].[Date],DATE(2021,1,1),DATE(2021,7,15))))
MAXINCURRED:
MAXINCURRED =
CALCULATE(
'clm ValuationData'[Total Incurred_NEW],
FILTER('clm ValuationData',
('clm ValuationData'[MAXVAL])))
MINVAL: MinVAL = CALCULATE([Total Incurred_NEW],
FILTER('clm ValuationData',MAX('clm ValuationData'[ValuationDate])),
PREVIOUSYEAR(DATESBETWEEN('clm Claims'[LossDate].[Date],DATE(2021,1,1),DATE(2021,7,15))))
MININCURRED =
CALCULATE(
'clm ValuationData'[Total Incurred_NEW],
FILTER('clm ValuationData',
'clm ValuationData'[MinVAL]))
If the used Dax is not upto the mark, please help me achieve the above ask if there are any other methods.
Please help.
Thanks,
glad
Hi @Anonymous,
Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @Anonymous,
I'd like to suggest you create a calculated table with the date value you required and use it on the legend field, then you can write measure expressions to extract the current category and redirect to particular calculate formulas that filter by the current legend:
If the above does not help, can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hello @v-shex-msft ,
Can i have some update?
To breif you more about it. I need to compare Top 5 causes of Loss Range for the year 2021 with Same top 5 causes of 2021 but the loss range year would be 2020.
Hello @v-shex-msft ,
Thank you for the reply. I would bring more info regarding the ask. It basically means,
The user wants to compare the most recent top 5 causes of loss to the same period last year. In other words, compare the top 5 causes for the first 7 months of 2021 to what they were for the first 7 months of 2020 for both severity and frequency.
Used Pivot tables to determine values and mocked up the below comparison Chart.
Hi @Anonymous,
Did you mean to rank and pick the top 5 categories based on the current date filter and show them on a chart and groups by categories, split by date legends, right?
If that is the case, you may need to do the following steps.
1. Create an unconnected date table, it will be used as legends on the chart.
2. Drag the Category field to the axis of the chart.
3. Write a measure expression and use it on the value fields of your chart.
For the measure formula:
1. It needs a variable to summary table records and ranking records based on date and causes then you can extract the match causes to a list.
All the secrets of SUMMARIZE - SQLBI
2. It required if statement to check the filtered date if they are included in the last three years with the current month.
3. It required if statement to check if the current cause is included in the list of ranked cause list.
4. If current values matched all the conditions, you can use the current date and current cause as conditions to filter and summary calculated results.
Regards,
Xiaoxin Sheng
Hello @v-shex-msft
Thans for the reply, i will certainly try to follow the method mentioned. But just to be more clear, what i am looking for is I have a slicer for valuation dates which has 2 dates in it. 7-15-2020 and 7-15-2021. Both these valuation dates have a loss date range separtly starting from 01-01-2018 till end of 2021 respectively. What I am looking for is, I need to find the top 5 causes of loss for the most recent valuation date from 01-01-2021 till loss range till 07-15-2021 and the same top 5 causes of loss for the 07-15-2021 but the loss data should be the 01-01-2020 till 07-15-2020. Basically showing the top 5 causes of loss for the 2021 validation date with same top 5 causes of 2021 but values of loss raning from 1-1-2020 till 7-15-2020.
Hope i was clear. I would like to know the Dax queries to use in order to achieve it.
Thanks,
gladson
You would use a calendar table in your data model. That table would have a month number column. You would then filter that column to only include months 1 through 9.