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!How to "apply" results from a virtual table to a measure, for a visual?
I created a virtual table just to "check" that I can achieve the results I'm after. What I'm stuck at now, is how to then USE that equivalent virtual table within a measure, to be executed across the "full" timescale. So ideally, as it's iterating through the table, it would associate to the evaluation context offered by the matrix visual. I suspect implementing a RANKX rather than a TopN could be helpful too, that's one of the other avenues I'm exploring... Thanks in advance for any tips or suggestions! I will update if/when I have a breakthrough on my end as well. (I apologize in advance for having to obscure things, but it's all I have authority to share at this time) Table = VAR __Year = MAX( DIM_Dates[Year] ) VAR __Month = MAX( DIM_Dates[Month] ) VAR __Hour = MAX( DIM_Hours[Hour] ) VAR __Market = MAX( DIM_MarketRegions[Market] ) VAR SystemTable = FILTER( ADDCOLUMNS( SUMMARIZECOLUMNS( DIM_Dates[Year], DIM_Dates[Month], DIM_Dates[Day], DIM_Hours[Hour], DIM_MarketRegions[Market] ), "@value", [System value] ), [Year] = 2019 && [Month] = 6 && [Market] = "Name" ) VAR TopNTable = TOPN( 5, SystemTable, [@value], DESC ) VAR Result = TopNTable RETURN Result And here is the added column that takes care of the count CountRowsFiltered = VAR __Hour = [Hour] VAR Result = CALCULATE( COUNTROWS( 'Table' ), FILTER( 'Table', [Hour] = __Hour ) ) RETURN Result I've tried attaching some sample data as a *.xlsx, *.csv, and *.txt but it says those file types are not supported... Here is an example of what a single day looks like 1/1/2019 0:00 1 5568.738 1/1/2019 0:00 2 69891.03 1/1/2019 0:00 3 12807.08 1/1/2019 0:00 4 68851.35 1/1/2019 0:00 5 2755.848 1/1/2019 0:00 6 78801.34 1/1/2019 0:00 7 67741.83 1/1/2019 0:00 8 43061.47 1/1/2019 0:00 9 80554.17 1/1/2019 0:00 10 77910.99 1/1/2019 0:00 11 54467.13 1/1/2019 0:00 12 46198.4 1/1/2019 0:00 13 49248.13 1/1/2019 0:00 14 76489.75 1/1/2019 0:00 15 1495.989 1/1/2019 0:00 16 67858.69 1/1/2019 0:00 17 10028 1/1/2019 0:00 18 44961.23 1/1/2019 0:00 19 74369.47 1/1/2019 0:00 20 60877 1/1/2019 0:00 21 15663.33 1/1/2019 0:00 22 36276.2 1/1/2019 0:00 23 36913.25 1/1/2019 0:00 24 22711.02
