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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors