DAX TRETAS Help

[abhiram342]

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:

CountryId	MonthKey	SalesQuantity
1	20201201	100
1	20210101	200
1	20210201	300
2	20201201	100
2	20210101	200
2	20210201	300

Dimension Table:

DimCountry

CountryID	CountryName
1	US
2	India

Dim Normalized Sales:

CountryId	MonthKey	NormalizedSalesDate
1	20201201	0
1	20210101	1
1	20210201	2
2	20201201	0
2	20210101	1
2	20210201	2

DimDate:

DateKey	MonthKey	No of Days in Fact Sales
20201201	20201201	31
20201202	20201201	0
20201231	20201201	0
20210101	20210101	31
20210102	20210101	0
20210131	20210101	0
20210201	20210201	28
20210201	20210201	0
20210228	20210201	0

Dim IsLatest:

Holds latest date

Latest Sales Month	IsLatest
20210201	TRUE

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

[amitchandak]

@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))

[abhiram342]

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