Debug DAX measure variables incl. virtual tables
Hi, I created an idea to be able to visually inspect the variables of a DAX measure, when the DAX measure is used in a table visual, line chart visual, etc. Please vote 😀 https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a7a31361-2f40-ef11-b4ac-6045bdbf86b4
Calculate accumulated product sales ending expiry dates using Dax
I was trying to calculate accumulated sales ending expiry dates. The target is to calculate the accumulated sales till the month the product expires. I used a measure to calculate it. However it didn't work. Can any expert advise how the measure can be adjusted or re-developed to suit this scenario? Thanks very much! The link to the PBI report is below: Accumulated sales PBI report I have a list of products with the following expiry dates: Product Code Expiry Month A 202205 B 202206 C 202207 The sales by month for them are as follows: Calendar month Product Code Amount 202204 A 1000 202205 A 1000 202206 A 1000 202207 A 1000 202208 A 1000 202204 B 1000 202205 B 1000 202206 B 1000 202207 B 1000 202208 B 1000 202204 C 1000 202205 C 1000 202206 C 1000 202207 C 1000 202208 C 1000 The desired output of accumulated sales is as follows: Calendar Month A B C Total 202204 1000 1000 1000 3000 202205 2000 2000 2000 6000 202206 3000 3000 6000 202207 4000 4000
Remove Table Filters While Using Virtual Table Measures
Hello! I'm currently trying to return the max value from a virtual table that I will use in another virtual table, along with removing the table filters when I place this max value in a table visual. Specifically, the 'MAX Efficiency Score' measure will be utilized in the 'Efficiency Score' measure (see 'var table7' in the 'Efficiency Score' measure). Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var table7 = ADDCOLUMNS(table6, "FinalScore", [Score]/[MAX Efficiency Score]) var table8 = SUMX(table7, [Score]) return table8 MAX Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var final = ADDCOLUMNS(table6, "MaxValue", MAXX(table6, [Score])) return CALCULATE(MAXX(final, [MaxValue]), ALL('Fuel (Traveling)')) My current issue is that I can't get the 'MAX Efficiency Score' measure to return the max score of 79.58 (in the case below) for each row of my table visual below. For background, the table visual below contains the same fields (Driver Fleet Name and DriverName/ID) that are used in the SUMMARIZE functions above. I also set the dashboard up so the 'Efficiency Score' and 'MAX Efficiency Score' values dynamically update when the date slider is used, which is why I referenced the 'Combined Calendar[Date - Copy] field in the measures above. I'd really appreciate any help I could get on this issue. Thanks!
Lookup between virtual tables without relationship
Hi guys, I am quite new to DAX magic and been practicing a lot in the last few months, but still I can't fully understand the concept of lookup values based on columns from "virtual tables", even though I've read the articles about it, and also about the TREATAS function that probably should help here. No matter which method i've used in my code to add the column of lookup value from one table to another, I'm getting the same message of "table variable "xxx" cannot be used in this concept, because a base table is expected" 😕 Anyway, my example is very simple: DEFINE VAR _size = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'person'[person id] ), "total", 'measures'[total] ) VAR _tbl = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'person'[person id], 'mgr'[mgr id] ), "lookup", LOOKUPVALUE ( '_size'[total], '_size'[person id], '_tbl'[mgr id], 0 ) ) EVALUATE _tbl definitions: 'person' and 'mgr' are actual physical tables, [total] is a meassure as part of the model. so the idea is to add a new column to _tbl1 called "lookup" that will bring the [total] for each [mgr id] matching [mgr id] to [person id]. so as mentioned the error i get is about the fact that those tables are not physical ones in my model, but a virtual temp tables that i'm creating in thie query. so what's the method that can be used to do a lookup between non-related virtual table variables? thanks!
dynamic conditional formatting with virtual table and switch
Hi, im having an issue building dynamic conditional formatting on my visual (color columns) The reason why i need to build that are all spaces in between columns in a chart, if more trafo numbers are selected than the spaces gets bigger To do that i added a table with ColorID and HEX rgb. I want to build virtual table with all selected Serial Numbers from slicer, adding HEX rgb by matchin the ID from virtual table and colorID and then with switch return HEX rgb. The problem i have is that it always return same hex rgb with index nr 1 IndexColorTrafo = var CurrentSelection=MAX(MeasurementsByFrequency[Serial Number]) VAR SelectedTrafoIDs = VALUES(MeasurementsByFrequency[Serial Number]) VAR NumSelectedTrafoIDs = COUNTROWS(SelectedTrafoIDs) VAR VirtualTable = ADDCOLUMNS( GENERATESERIES(1, NumSelectedTrafoIDs), "TrafoID", SELECTEDVALUE(MeasurementsByFrequency[Serial Number]), "ColorID", LOOKUPVALUE(TblCSSColor[Hex rgb],TblCSSColor[ColorID],[Value]) ) RETURN VAR Output= MAXX( VirtualTable, //VAR CurrentTrafoID = [TrafoID] //RETURN IF( NumSelectedTrafoIDs = 0, BLANK(), SWITCH( TRUE(), //[TrafoID]=CurrentSelection, CONTAINSSTRING(SelectedTrafoIDs, CurrentSelection), [ColorID], BLANK() ) ) ) Return Output anyone can help?
