Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
abhiram342
Employee
Employee

DAX TRETAS Help

Hi All,

 

I'm facing issue with Treatas function. Can someon please assit.

Goal: We want analyze Sales based in "NormalizedSalesDate". I want pass (CountryId) to DimCountryId and Date Key to DimDate and finally both dims should filter Facts

 

Tables:

Fact Sales:

CountryIdMonthKeySalesQuantity
120201201100
120210101200
120210201300
220201201100
220210101200
220210201300

Dimension Table:

DimCountry

CountryIDCountryName
1US
2India

Dim Normalized Sales:

CountryIdMonthKeyNormalizedSalesDate
1202012010
1202101011
1202102012
2202012010
2202101011
2202102012

DimDate:

DateKeyMonthKeyNo of Days in Fact Sales
202012012020120131
20201202202012010
   
20201231202012010
202101012021010131
20210102202101010
   
20210131202101010
202102012021020128
20210201202102010
   
20210228202102010

Dim IsLatest:

Holds latest date

Latest Sales MonthIsLatest
20210201TRUE

 

Relationship:

Fact Sales [MonthKey]-->DimDate[DateKey]

Fact Sales [CountryId]-->DimCountry[CountryId]

Fact Sales [MonthKey]-->Dim IsLatest:[Latest Sales Month]

 

Dim Normalized Sales[Month Key]:-->DimDate[DateKey]

Dim Normalized Sales[CountryID]:-->DimCountry[CountryId]

 

Measure:

Total Sales =
VAR LatestDate = MAX('Dim IsLatest'[Latest Sales Month])
VAR LatestData = CALCULATE(SUM(Fact Sales[Sales]), 'Dim Date'[DateKey] = LatestDate)
RETURN IF(ISCROSSFILTERED('Dim Date'[Date Key]), CALCULATE(SUM(Fact Sales[Sales])), LatestData)

 

TotalSalesNormalized = CALCULATE(SUM('Fact Sales'[Total Sales]),TREATAS(VALUES('Dim Normalized Sales'[Month Key]),'DimDate'[date key]))

Average Sales=
VAR TotalSales = 'Fact Sales'[Total Sales]
VAR NoOfDays = IF(ISCROSSFILTERED('Dim Date'[DateKey]),SUM('DimDate'[No of Days in Fact Sales]), 1)
VAR NoOfDaysNormalized = CALCULATE(SUM('DimDate'[No of Days in Fact Sales]),TREATAS(VALUES('Dim Normalized Sales'[Month Key]),'DimDate'[Date key]))
VAR AvgsalesNormalized = DIVIDE([TotalSalesNormalized], NoOfDaysNormalized, 0)
VAR AvgSales = DIVIDE(TotalSales, NoOfDays, 0)
RETURN IF(ISCROSSFILTERED('Dim Normalized Date'[Normalized DAte]),AvgSalesNormalized,AvgSales)

 

 

Issue:

We are seeing issue with Treatas here, The goal here is Avg sales should work fine when ever we drag Date and  NormalizedSalesDate drag Normalized date. Currently seeing issue when ever we drag NormalizedSalesDate ,

Ideally it should get distinct dates from Dim Normalized Sales Date Table and filter date table and date table should filter Fact Table.

Please assit how to write query like this in DAX

SELECT SUM(SALES) FROM Factsales where MonthKey in (

Select distinct date key from DimDate where DateKey in (select distint DateKey from DimNormalizedDate))

 

NotE: we don't have bidirecitonal relationship between Dim Date and Dim Normalized Sales

Thanks,

Abhiram

 

2 REPLIES 2
amitchandak
Super User
Super User

@abhiram342 , Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

 

Try a measure like

measure =
var _tab = distinct(DimNormalizedDate[DateKey])
var _date = distinct(filter(DimDate, DimDate[DateKey] in _tab), DimDate[MonthKey])
return
calculate(sum(Sales[SALES]), filter(Sales,Sales[MonthKey] in _date))

 

Hi Amit - Thank you for your response. It has sensitive data, hence I have shared sample data.

Below line is not working. Can you please help here

Error: containsrow must have a value for each row 

var _date = distinct(filter(DimDate, DimDate[DateKey] in _tab), DimDate[MonthKey])

 

Thanks,

Abhiram

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.