Adding Quantity of Late Items for Each Year Using DAX
Hello All, I have been working on this DAX for a few days and am not getting the desired result, so figured I would try posting here for a change. Our IT department owns the data and I am unable to add columns within the tables themselves, and must get to what I need via DAX. Our organization deals with "certifications" which are annual income certifications for housing. Sometimes they are behind on recertifying, but our current data does not multiply the past due certification to tell us the quantity of certifications we are behind. I.e. if the last certification was due in March 2020, and was not completed, we are actually missing certifications for March 2020, March 2021, March 2022, and March 2023 - a quantity of 4. But our current system says we only have 1 late certification, which is what I am trying to adjust with this measure. I am trying to use DAX to add a "multiplier" (i.e. add quantity of late items for each additional year) to the current quantity based on the year of the "Next Certification" and what date I select for the report to be "as of" (Slicer Date). It seems I'm close, but whenever the current quantity is "2" (i.e. if it is registering that we had 2 late certifications due in 2020), my measure seems to be adding 2 for each additional year, and is giving me 8 instead of 5 for the late certs with multiplier. Even if our late quantity starts as 2, I only want the measure to add 1 certification for each additional year. Table Name: Certifications Next Certification Reference Column: NextCertification as Date Filter Reference Date: SlicerDate[SlicerDate] Current (incorrect) column with # of late certifications: LateCertificationsNeverFinished Here's what I have for the DAX currently: Total Late Certs with Multiplier = if(hasonefilter(Certifications[NextCertification]), calculate(if([NextCertification as Date]<SlicerDate[SlicerDate], if(DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)>365, value([LateCertificationsNeverFinished])+(ROUNDDOWN((DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)/365),0)), value([LateCertificationsNeverFinished])), value([LateCertificationsNeverFinished])), filter(values(Certifications),Certifications[FamilyIsCurrent]<>0)), sumx(filter(certifications,[LateCertificationsNeverFinished]>0 && Certifications[FamilyIsCurrent]<>0), if([NextCertification as Date]<SlicerDate[SlicerDate], if(DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)>365, value([LateCertificationsNeverFinished])+(ROUNDDOWN((DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)/365),0)), value([LateCertificationsNeverFinished])), value([LateCertificationsNeverFinished])))) Thank you for any help!
Using HASONEFILTER/SUMX to correct subtotals makes forcast totals disappear
Howdy Friends, Here is an odd problem for this Friday: Background I have a data model where I am forecasting out to 2030, the number of minutes on different types of medical imaging equipment based on the historical growth rates in the data. My first step was to summarize the data set into a SummaryMinutes table that is grouped the way I want to do the forecast (grouping by [VolumeSort], [Modality], and [FiscalYear]). This gives me a nice tidy table to work with. The second step I did was to calculate the compound annual growth rate in the summary table between 2018 and 2022. There are a few measures to make this work but the result is in [CAGR Historical]. Next, the client would like to override the growth rate for three of the four [VolumeSort] groupings and leave the historical growth rate for the "Other" category. I did this with a SWITCH function in the [CAGR Future] measure. CAGR Historical = // calculated growth rate from 2018 to 2022 VAR Periods = 2022 - 2018 + 1 RETURN RRI ( Periods, [Minutes FY18], [Minutes FY22] ) CAGR Future = // override the growth rate for 3 of the 4 [Volume Sort] groupings, keep the calculated growth rate for the "Other" [Volume Sort] grouping CALCULATE ( SWITCH ( SELECTEDVALUE ( SummaryMinutes[Volume Sort] ), "Cancer IP", 0.051, "Cancer OP", 0.111, "Emergency", 0.040, [CAGR Historical] ), ALL ( LookupFiscalYear ) ) The Problem The third step is where the trouble starts. I created a measure called [Minutes Forecast] that uses the FV function and the growth rates from [CAGR Future] to forecast the minutes out to 2030. The forecast values are correct and as expected the subtotals do not add up correctly. To fix the subtotals I created a measure called [Minutes Forecast Subtotal] where I used the HASONEFILTER/SUMX of VALUES pattern to correct the subtotals. I am grouping the results on two levels, [VolumeSort] and [Modality]. Therefore I nested the HASONEFILTER/SUMX pattern to make the totals work across both levels. However, instead of correctly summing subtotals, the subtotals disappeared for all the future forecasted values! I've used this technique many times in the past but I can't workout out what is making the subtotals disappear in this model. Minutes Forecast = // forecast total minutes from 2023 through 2030 using [CAGR Future] growth rates - FV ( [CAGR Future], SELECTEDVALUE ( LookupFiscalYear[FiscalYear] ) - 2022, 0, [Minutes FY22] ) Minutes Forecast Subtotal = // correct subtotals at the [Modality] grouping level IF ( HASONEFILTER ( SummaryMinutes[Modality] ), // nested correct subtotals at the [Volume Sort] grouping level IF ( HASONEFILTER ( SummaryMinutes[Volume Sort] ), [Minutes Forecast], SUMX ( VALUES ( SummaryMinutes[Volume Sort] ), [Minutes Forecast] ) ), SUMX ( VALUES ( SummaryMinutes[Modality] ), IF ( HASONEFILTER ( SummaryMinutes[Volume Sort] ), [Minutes Forecast], SUMX ( VALUES ( SummaryMinutes[Volume Sort] ), [Minutes Forecast] ) ) ) ) Results As you can see in these two report pages, if just use the [Forecast Minutes] I get the wrong subtotals as expected. But on the second page, you can see that my corrected subtotals pattern makes the subtotals only for future values disappear! Here is a Box link for the PowerBI file: https://app.box.com/s/e1bas1vj2szjxke3zo2ppskq6vihzcwl Thanks for any help!
HasOneFilter Issue
Hi All, I am creating a drillthrough button in my report and for which I am using HASONEFILTER to see if a filter is applied or not. I also have a slicer (dropdown) in my page. Basically what the formula does is checks if user selects a value from the slicer and if selected it give me a page name. The page name is used as a navigation to drillthrough page. Below is my DAX MyDrillthrough Navigation = Var _isValueSelected = HASONEFILTER('Project List'[ORG]) Return IF(_isValueSelected ,"Details Page","") I one slicer dropdown for 'Project List'[ORG]) and another visual of a small metrix table as 'Project List'[ORG]) and Cost So now the strange part, when I select ORG from dropdown the drillthrough button doesn't work. BUT if I select ORG from the metrix visual then the drillthrough button works... I am sure dropdown is the issue ? Does HASONEFILTER doesn't work with slicers ? Please help.
