Forum Discussion
group by custom date
mikoal , First of all summarize will create a calculated column, which will not take any slicer filter
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
I'll try my best to explain.
Goal: To filter data based on percent slicer.
Date slicer for customizable date range.
Slicer = User selects a percentage and date range
We do a comparison with calculated Data Percent VS the slicer %. If the calculated percent is less than slicer %, that customer is filtered out and not displayed.
Calculated Data% = (rowcount of hourly data) divide by (hours of time frame)
If time frame is 1 year, hours of time frame = 8760
This is what you see here.
The following visual, I am able to filter the "premises" based on the percent (Data % vs slicer %)
The issue comes when the calculated % applied.
when I enter Data% into the following visual, the rowcount would be 1. Then the data % would be 1/8760 = almost 0 and the slicer will filter it out.
So I used the following
MatrixIntervalcount = SUMMARIZE(loadshape,"rowcount",CALCULATE(countrows(ALLSELECTED(loadshape)),ALLEXCEPT(loadshape,_Dimdate[Fiscal Year],loadshape[Premise])))
Which allows me to keep the interval count constant . As you can see for premise 6355672_3 it shows 6053 rowcount at every single row. Which allows me to do the correct calculation 6053/8760 = 69%
Then by using the following code, I am able to filter the visual properly.
MatrixFilteredhourlyData =var datapercent=SUMMARIZE(loadshape,"rowcount",CALCULATE(countrows(ALLSELECTED(loadshape)),ALLEXCEPT(loadshape,_Dimdate[Fiscal Year],loadshape[Premise])))VAR value1 = 'PercentSelect'[Percent Value]/100returnIF((datapercent/[Dateinterval])>=value1,loadshape[Hourlyusagesum],BLANK())Unfortunately the method I used is isolated to a timeframe from the Allexcept _dimdate[fiscalyear]. This works but only if I restrict it to fiscal year.
If I used _Dimdate[month], then i will need to restrict users to selecting a month.
This isn't flexible , i want the user to be able to select the date from a slicer. But if I allow that, then the code I use wont work.
If you require more clarity I can try to send a sample file.
Here is a sample data
The desired results is this matrix. The columns filter in and out depending on the Percent slicer.
