Top N and others in calculation group
Hi community I have followed this great material by SQLBI to do a dynamic TopN & Others solution : https://www.sqlbi.com/tv/implementing-the-top-n-and-others-pattern-using-calculation-groups-in-dax-unplugged-28/ I have a issue with incorrect Ranking. I can't figure out why.. Any ideas what to do? In some filter cases it return correct ranking, but mostly incorrect. Relevant measures below: Ranking = IF ( ISINSCOPE ( 'Customer Names'[Customer_Name] ), VAR CustomersToRank = [TopN Value] VAR MeasureForRanking = SELECTEDVALUE ( 'Ranking measure'[Measure to use] ) VAR Val = [ValueForRanking] VAR IsOtherSelected = SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others" RETURN IF ( IsOtherSelected, -- Rank for Others CustomersToRank + 1, -- Rank for regular Customers IF ( Val > 0, VAR VisibleCustomers = CALCULATETABLE ( VALUES ( 'pbi DimCustomer' ), ALLSELECTED ( 'Customer Names' ) ) VAR Ranking = RANKX ( VisibleCustomers, [ValueForRanking], Val ) RETURN IF ( Ranking > 0 && Ranking <= CustomersToRank, Ranking ) ) ) ) ValueForRanking = VAR MeasureForRanking = SELECTEDVALUE ( 'Ranking measure'[Measure to use] ) VAR Val = SWITCH ( TRUE, MeasureForRanking = "Sales", [Sales], MeasureForRanking = "Order intake", [Order intake], MeasureForRanking = "Order bank", [Order bank], [Sales] ) RETURN Val Calculation item: IF ( NOT ISSELECTEDMEASURE ( [Ranking] ), VAR ValueOfAll = CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Customer Names' ) ) RETURN IF ( NOT ISINSCOPE ( 'Customer Names'[Customer_Name] ), -- Calculation for a group of products ValueOfAll, -- Calculation for one product name VAR CustomersToRank = [TopN Value] VAR ValueOfCurrentCustomer = SELECTEDMEASURE () VAR IsOtherSelected = SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others" RETURN IF ( NOT IsOtherSelected, -- Calculation for a regular product VAR Ranking = [Ranking] VAR Result = IF ( NOT ISBLANK ( Ranking ), ( Ranking <= CustomersToRank ) - ( Ranking = CustomersToRank + 1 ) ) RETURN IF ( Result, ValueOfCurrentCustomer ), -- Calculation for Others VAR VisibleCustomers = CALCULATETABLE ( VALUES ( 'pbi DimCustomer' ), ALLSELECTED ( 'Customer Names'[Customer_Name] ) ) VAR CustomersWithValues = ADDCOLUMNS ( VisibleCustomers, "@ValForRanking", [ValueForRanking], "@SelMeasureAmount", SELECTEDMEASURE () ) VAR ValueOfTopCustomers = SUMX ( TOPN ( CustomersToRank, CustomersWithValues, [@ValForRanking] ), [@SelMeasureAmount] ) VAR ValueOfOthers = ValueOfAll - ValueOfTopCustomers RETURN ValueOfOthers ) ), SELECTEDMEASURE () ) Would appreicate any ideas/help to fix this Thanks
Unable to retrieve unique case number based on Message Date in Power Bi Desktop
Hello All, Can someone please help me to resolve the below issue. I have this table where in message date is the response received on the case number) and date/time closed is when the case was closed. I am trying to display only unique casenumber based on the latest message date along with timestamp. As you can see "00854913" case number is displayed twice as the message date timestamp is 6:55 PM & 1:47 PM.
Data Labels and Formatting via calculation group
Hi everyone, I stumbled across some weird behaviour concerning formatting via calculation items and data labels. So I have a calculation group with two items "None" - applies nothing Expression: SELECTEDMEASURE() Format String Expression: SELECTEDMEASUREFORMATSTRING() "Formatted" - should format the number to millions with no decimal places: Expression: SELECTEDMEASURE() Format String Expression: "#,#0,,.M" Now this calcualtion group does indeed work with e.g. tables but when I want to use this in data labels it does not work, instead it simply adds an "M" at the end of the value, without formatting the value: Are data labels somehow calculated differently? Or could it simply be a bug because of the new features regarding dynamic data labels and such? Perhaps someone here can shed some light! Thank you 🙂Dynamic slicers for time intelligence not working as expected
I have a golden dataset that serves as the basis for many reports and for adhoc data exploration. It has a star schema with one FACT table. My problem is with creating dynamic date slicers for time intelligence. The FACT table has data for month-end dates and current MTD weekday dates only, that is not continuous dates. All measures are in a measures table, FACT table hidden from dataset users. We have a designated date table with continuous dates from 2015 through 2024, which more than the time frame of the FACT table. Using DAX, we created a Relative Time table that has business relevant filters defined, such as Current Date (not sysdate but latest date from FACT table), Last Audited Month (again per FACT table filters), etc. This table allows for dynamic slicers on report pages. Recently we were asked to add time intelligence calculations using calculation groups to this dataset, which was not a problem. We created items like: Selected Month, Prior Month, MoM, MoM%, Selected QTD, Prior QTD, QoQ, QoQ%, etc. See below picture. The definitions are based on the date table as it has continuous dates. This all works as long as the filter on the report is based on the date table. However, it does not work with the nested Relative Time, 'Date Table' As of date combined filter's dynamic filtering. The problem is that if a Relative Time is selected from the filter, the filtering does not work. Shows the proper calculations for every date in the FACT table, no filtering. If a date is selected then it filters for that date but then the slicer is not dynamic, following month has to be reset. Our requirement is to have the time intelligence calculations work with the dynamic filters. Here is the sample DAX from the time intelligence calculation group: CALCULATIONGROUP '0 Relative Time Calculations'[Time Measures] CALCULATIONITEM "Selected Month" = CALCULATE( SELECTEDMEASURE() ,CROSSFILTER( '0 Date Table'[As Of Date], '0 Relative Time'[ASOF_DT], BOTH ) ) Ordinal = 0 CALCULATIONITEM "Prior Month" = CALCULATE( SELECTEDMEASURE() , PREVIOUSMONTH('0 Date Table'[As Of Date]) , CROSSFILTER( '0 Date Table'[As Of Date], '0 Relative Time'[ASOF_DT], BOTH ) ) Ordinal = 1Calculation Group is Overriding My Measures
Hi All - I'm using calculation groups, but it appears when i click into the visual where a calculation group value exists, the filter context is messing with my other measures, and is in fact overwriting them. (ie bring back a value from the measure I clicked into, rather than the measure which the visual is based upon). I get that this is filter context getting passed to the visual and I assumed that using some sort of combination of all or remove filters pointed at the calculation group table would fix the issue. That doesn't appear to be the case. Is this a bug? Or is there a good way to handle this? Its worth noting that my calculation measures general start with selectedmeasure(). I have not yet attempted to rewrite the calculation groups with an if statement so that only the relevant measures are modified?
Calculation Group: Different filters for Current period and previous periods in aggregate time intel
Hi fellow dax enthusiasts I have KPI measures that I want to calculate the YTD and Rolling twelve (R12) values, however, the latest period needs to have extra filters compared to the previous x periods VAR _Agg = SELECTEDVALUE ( 'Slice_Aggregate'[Index] ) VAR _VerType = SELECTEDVALUE ( 'Slice_VersionTypes'[Index] ) //LP = Latest Period VAR _MaxDateLP = EOMONTH ( MAX ( 'Dimension Date'[FullDateAlternateKey] ), 0 ) VAR _MinDateLP = EOMONTH ( _MaxDateLP, -1 ) + 1 VAR _ResultLP = IF ( _VerType = 1, CALCULATE ( SELECTEDMEASURE (), FILTER ( ALL ( 'Dimension Date' ), 'Dimension Date'[FullDateAlternateKey] <= _MaxDateLP && 'Dimension Date'[FullDateAlternateKey] > _MinDateLP ), 'Fact DealerCompositeData'[IsLatestVersionSubmitted] = TRUE, 'Fact DealerCompositeData'[IsLatestVersionApproved] = TRUE, 'Fact DealerCompositeData'[IsSuppressed] = FALSE ), CALCULATE ( SELECTEDMEASURE (), FILTER ( ALL ( 'Dimension Date' ), 'Dimension Date'[FullDateAlternateKey] <= _MaxDateLP && 'Dimension Date'[FullDateAlternateKey] > _MinDateLP ), 'Fact DealerCompositeData'[IsLatestVersionOntime] = TRUE, 'Fact DealerCompositeData'[IsLatestVersionApproved] = TRUE, 'Fact DealerCompositeData'[IsSuppressed] = FALSE ) ) //PP = Preceding periods VAR _MaxDatePP = EOMONTH ( MAX ( 'Dimension Date'[FullDateAlternateKey] ), -1 ) VAR _MinDatePP = EOMONTH ( _MaxDatePP, -11 ) + 1 VAR _ResultPP = CALCULATE ( SELECTEDMEASURE (), FILTER ( ALL ( 'Dimension Date' ), 'Dimension Date'[FullDateAlternateKey] <= _MaxDatePP && 'Dimension Date'[FullDateAlternateKey] > _MinDatePP ), 'Fact DealerCompositeData'[IsLatestVersionSubmitted] = TRUE, 'Fact DealerCompositeData'[IsLatestVersionApproved] = TRUE ) VAR _MonthCount = CALCULATE ( COUNTROWS ( SUMMARIZE ( 'Fact DealerCompositeData', 'Fact DealerCompositeData'[ActivePBIParticipationDateKey] ) ) ) RETURN IF ( ISNUMBER(_ResultLP), IF ( _Agg = 1, _ResultPP + _ResultLP, DIVIDE ( ( _ResultPP * _MonthCount ) + _ResultLP, IF ( _Agg = 2, 1, _MonthCount ) ) ), _ResultLP ) to achieve this I created two variables one for the latest period and one for previous periods, this mainly works except I get odd results when my SelectedMeasure is based on an Average and also if I don't check the return value it attempts to convert text (measure names etc) into values hence the ISNUMBER check. Is there a better way of achieving what I have done but with one variable? I tried to join the FILTER function with && but its not allowed. note: when _VerType = 1 = Reported versions when _VerType = 2 = Latest versions when _Agg = 1 = SUM when _Agg = 2 = Average
Using Format function with Calculated Groups to convert numbers to millions doesn't work properly
Hello. I have some data with big numbers and what i want is to create a slicer so a user can choose what format he want to see: usual (like 1 234 567) or in millions (1.2 M). To make this i created a calculation group with two values - first one is nothing but a SELECTEDMEASURE() and the second one is FORMAT function (you can see it on screen). The problem that when i activate Mln option on slicer it works only with card or table visual but don't work with line or bar chart. I guess the problem may be because it convers it to text format and it can't calculate it properly or something else. Anyway, any suggestions to force it work on every visual? Without applying format: With format:
