TREATAS with Event Database
Hello community, I am attaching a sample PBI report via WeTransfer. I have an Employee Events table, where each employee transaction has an Effective Start Date and Effective End Date. I have FX rates tables, with MONTHLY records. For each date selected in the Date slicer, I need to calculate teh Annualized Salary in USD. The salary is in Local Currency, I got a measure for it: Annualized Salary (LC) = CALCULATE ( SUMX ( 'Employee Events', 'Employee Events'[Annualized Salary (LC)] ), 'Employee Events'[Effective Start Date] <= MAX ( 'Calendar'[Date] ) && 'Employee Events'[Effective End Date] >= MAX ( 'Calendar'[Date] ), REMOVEFILTERS ( 'Calendar' ) ) This is correct. I now need to calculate the Annualized Salary in USD at a given date (the users will have the possibility to select a date value in the `Calendar`[Date] slicer, always). The FX rate table has monthly records, so I created a virtual relationship between FX Rate, Calendar and Employee Events using TREATAS: FX Value 3 = CALCULATE ( MAX ( 'FX Rates'[Value] ), TREATAS ( SUMMARIZE ( 'Employee Events', 'Employee Events'[Currency], 'Calendar'[End of Month] ), 'FX Rates'[Currency], 'FX Rates'[Date] ) ) This works as long as for the Date selected in the slicer, I have an entry in Employee Events. So for example, if I select 1st of October, I will get data only for employees whose Effective Start Date = Date: User 3 does not have data, because he does not have any event on effective start date = 1st of October 2025... Expected outcome: if I select any date in the Date slicer, I should Annualized Salary (USD) and FX Value 3 for all users who are in teh database at the given point in time.
Different behavior of treatas
issue: when I use treatas on a variable table within a measure I am seeing different behavior if I treatas the column to a fact or dimension table. when I apply treatas to change the data lineage to the dimension table, the relationship does not hold and the filter from the dimension table is not considered. However when the treatas is pointed at the fact table (which has a many to 1 relationship to the same dimension table) the behavior appears to work correctly. is there something going on within treatas which requires a condition to work? Is it possible that if I am changing the data type within dax on the key this is messing it up?
Combing TREATAS, COTAINSSTRING and CONCATENATEX issue (Icon Map)
Hi all, I have quite a bespoke issue that I can seem to find a resolution for (either by my own working or by searching online). I have a column of data in one table that has a corresponding strings such as bellow: Legend NO2 Cat 19-22 40-44 19-22 34-37 19-22,28-31,40-43 You'll note that in the last entry, multiple caterogries are included as the object encompasses multiple catergories. I have a second table (which is actually more of a legend as this is being used for a map) with the same list of corresponding catergories. The idea is that when someone clicks a catergory, or multiple catergories, within that table, it will filter the table above. Because of some quite deep complexeties with the Icon Map visualisation I am using, I cannot use a direct relationship so I have to use TREATAS. I have come up with a DAX that almost gets me where I need to be and is as follows and uses a CONCATENATEX on the fitlering table to create a single object (as Icon Map does not like tables of multiple values) (legendcheck2 is the filtering context and SNAMaster is the primary dataset)(ignore the switch): Value = SWITCH('Measures Table'[SelectionMeasure],1,CALCULATE(SELECTEDVALUE(SNAMaster[NO2_mean]), TREATAS(SUMMARIZE(FILTER(SNAMaster (CONTAINSSTRING(CONCATENATEX(VALUES(legendcheck2[CAT]),legendcheck2[CAT],","), VALUES(SNAMaster[Legend NO2 Cat])))),SNAMaster[Legend NO2 Cat]),SNAMaster[Legend NO2 Cat]))) This allows the last object in the table to be included but only when all of the catergories listed are selected. So for example, as long as 19-22,28-31,40-43 are within the selection criteria, the object will show. However, if for example I wanted to select just 19-22, the object will not appear. I understand why this is, because it cannot find the string as intended, but I am at a loss for how to resolve this issue so that the object will remain active when filtered by any combination of values selected. So that, for example, if I have 19-22 and 40-43 selected, the object will remain active. Am I missing something obvious here or should I be trying something entirley different? Any help would be greatly appreciated!Filtering model on latest version of all attributes from all dimensions through DAX
Hello, I have a model that's comprised of multiple tables containing, for every ID, multiple rows with a valid_from and valid_to dates. This model has one table in that is linked to every other table (a table working as both a fact and a dimension). This fact has bi-directional cross filtering with the other tables. I also have a date dimension that is not linked to any other table. I want to be able to calculate the sum of a column in this table in the following way: - If a date range is selected, I want to get the sum of the latest value per ID from the fact able that is before the max selected date from the date dimension. - If no date is selected, I want to get the sum of the current version of the value per ID. This comes down to selecting the latest value per ID filtered on the dates. Because of the nature of the model (bi-directional with the fact/dimension table), I want to have the latest version of any attribute from any dimension selected in the visual. Here's an data example and the desired outcome: fact/dimension table: ID Valid_from Valid_to Amount SK_DIM1 SK_DIM2 1 01-01-2020 05-12-2021 50 1234 6787 1 05-13-2021 07-31-2021 100 1235 6787 1 08-01-2021 12-25-2021 100 1236 6787 1 12-26-2021 12-31-2021 200 1236 6787 1 01-01-2022 12-31-9999 200 1236 6788 Dimension 1: ID SK Valid_from Valid_to Name 1 1234 10-20-2019 06-01-2021 Name 1 1 1235 06-02-2021 07-31-2021 Name 2 1 1236 08-01-2021 12-31-9999 Name 3 Dimension 2: ID SK Valid_from Valid_to Name 1 6787 10-20-2019 12-31-2021 Name 1 1 6788 01-01-2022 12-31-9999 Name 2 My measure is supposed to do the following: - If no date is selected than the result will be a matrix like the following: Dim 1 Name Dim 2 Name Amount Measure Name 3 Name 2 200 - If July 2021 is selected than the result will be a matrix like the following: Dim 1 Name Dim 2 Name Amount Measure Name 2 Name 1 100 So the idea here is that the measure would filter the fact table on the latest valid value in the selected date range, and then the bi-directional relationships will filter the dimensions to get the corresponding version to that row with the max validity (last valid row) in the selected range date. I have tried to do the following two DAX codes but it's not working: Solution 1: Amount Measure= VAR _maxSelectedDate = MAX(Dates[Dates]) VAR _minSelectedDate = MIN(Dates[Dates]) VAR _maxValidFrom = CALCULATE( MAX(fact[valid_from]), DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate) || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate) ) RETURN CALCULATE( SUM(fact[Amount]), fact[valid_from] = _maxValidFrom ) Solution 2: Amount Measure= VAR _maxSelectedDate = MAX(Dates[Dates]) VAR _minSelectedDate = MIN(Dates[Dates]) VAR _maxValidFromPerID = SUMMARIZE( FILTER( fact, DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate) || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate) ), fact[ID], "maxValidFrom", MAX(fact[valid_from]) ) RETURN CALCULATE( SUM(fact[Amount]), TREATAS( _maxValidFromPerID, fact[ID], fact[valid_from] ) ) Now the problem here is that with the first solution, filtering on other dimension work and I get the last version in the selected date range for all attributes of all used dimensions. But the problem here is that the max valid from is not calculated per ID, so I only get the max valid from overall. With the second solution, I do get the right max valid from per ID and the resulting number is correct, but for some reason, when I use other attributes from the dimensions, it duplicates the amount for every version of that attribute. So if somebody can explain this behaviour that will be great, and also, more importantly, if you have any solution to have both the latest value per ID and still keep filtering on other attributes, that would be great! Sorry for the long post, but I thought it's best to give all the details for a complete understanding of my issue, this has been picking my brain since few days now and I'm sure I'm missing something stupid but I turned to this community for help because I cannot seem to be able to find a solution! Thank you very much in advance for any help!
TREATAS filter propagation
Hello Given the following data model: I would like to create a measure that would aggregate an attribute from the table [ServiceCases]. The design should allow me also to slice [ServiceCases] table using [SalesInvoiceDate] time table. This is the initial code I created: Some fee = CALCULATE( [# ServiceCases], -- COUNTROWS(ServiceCases) USERELATIONSHIP(SalesInvoiceDate[SalesInvoiceDate],SalesOrder[InvoiceDate]), -- to activate the relationship TREATAS(values(ServiceCases[RelatedDocumentNo]),SalesOrder[InternDocumentNumber]) -- to map one table into another) ) Unfortunately, when I put attributes and the measure I'm interested in, I get record multuplication: Can anyone explain to me this strange behaviour?TREATAS working as a calculate filter in DAX Studio but not Power BI Desktop
Ok, here goes: I want to create a measure in Power BI Desktop that is SUM ( 'fact Back Order Demand'[Ordered Units Backordered] ) *but* only returns a value for Items (products) with the Top N number of backordered units. The following query in DAX Studio works exactly how I want it to: EVALUATE VAR Top_N = MAX ( 'TopN'[TopN] ) VAR filtered_items = SELECTCOLUMNS ( TOPN ( Top_N, SUMMARIZE ( 'fact Back Order Demand', 'fact Back Order Demand'[OrderedItemKey], "OUBamt", SUM ( 'fact Back Order Demand'[Ordered Units Backordered] ) ), [DUBamt] ), "OrderedItemKey", [OrderedItemKey] ) RETURN SUMMARIZECOLUMNS ( 'Item Details (Ordered Items)'[Item Description], "OUB", CALCULATE ( SUM ( 'fact Back Order Demand'[Ordered Units Backordered] ), TREATAS ( filtered_items, 'fact Back Order Demand'[OrderedItemKey] ) ) ) I can set Top_N = 5, and the query returns the following: There's a tie, so it returns 6 records, but it's acting exactly how I want. So what's the problem? I *cannot* get the measure to function in PBI Desktop: It returns data for all items, not just the Top 5! As far as I can tell, TREATAS is not working to filter the CALCULATE(SUM()) but I don't know why. What am I missing? Is there any alternative solution? Thanks!TREATAS returns blanks where it should not.
Hello, I am using TREATAS to return a text field in a measure where I'm not able to join the tables directly due to circular referencing with RLS. However, although it is returning a lot of values, it is also returning some blank rows where I would expect data to be returned, does anyone know why this may be? I started using one field to join on: VAR _id = CALCULATETABLE( VALUES( report_pm_change[asset_unique_id] )) RETURN CALCULATE ( SELECTEDVALUE(report_asset_change[status]), TREATAS( _id, report_asset_change[asset_unique_id] ) ) However when this did not work, I tried to join on two fields with the same inconsistent result: VAR _id = SUMMARIZE( report_pm_change, report_pm_change[asset_unique_id], report_pm_change[asset_number] ) RETURN CALCULATE ( SELECTEDVALUE(report_asset_change[status]), TREATAS(_id, report_asset_change[asset_unique_id],report_asset_change[reference] ) ) Any input would be welcome. Thanks, Mark
