Forum Discussion
DAX query for the below request
Hello community, My data is something like above where a company belongs to different Sides based on the projects. Every project has a start date and project active date. I want to calc...
Like this?
To get this, I converted the date columns from text to date data type, created a new calculated table DimDate using CALENDARAUTO() and defined the following measure:
CountSides = VAR SelectedDate = MAX ( DimDate[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Sample'[Sides] ), 'Sample'[EndDate] >= SelectedDate, 'Sample'[StartDate] < SelectedDate )
AlexisOlson
Super User
Super UserLike this?
To get this, I converted the date columns from text to date data type, created a new calculated table DimDate using CALENDARAUTO() and defined the following measure:
CountSides =
VAR SelectedDate = MAX ( DimDate[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Sample'[Sides] ),
'Sample'[EndDate] >= SelectedDate,
'Sample'[StartDate] < SelectedDate
)
This helps but what I exactly want is a measure based on count of sides : 1(Burger),2(burger+fries),3(happy meal) and 4(happy meal+nuggets) and then show the count of companies that belong to different category at a time
- AlexisOlson
Like this?
For this, I created a new table Sides with a single column Count with values 1 through 4 and a new measure
CompanyCount = VAR SideCount = SELECTEDVALUE ( Sides[Count] ) RETURN SUMX ( VALUES ( 'Sample'[Company] ), IF ( [CountSides] = SideCount, 1 ) )
Thanks Alexis 😊 You are awesome. Solution worked for me