Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 calculate the number of sides for a company between start date and project active date and based on that want to calculate how many companies at a particular time between start date and project active date belong to which category.

 

Category based on number of sides ::: count (1) = burger, count(2)= burger+fries, count(3)=happy meal 

 

For example,

Mcdonalds has 3 sides : MS, PS and SD and 3 projects : A, B and C.

Mcdonalds will be active from 2017-08-11 to 2022-08-01

Now, from (startdate)2017-08-11 to 2018-01-09 it had one side MS project A so Burger category and from 2018-01-10 one more project C was added so Burger+fries till 2019-08-01 when project B was added so now its an happy meal but now we also have to include project active date of projects while counting the number of sides.

All projects are active but on 2021-01-10 we loose 1 so now it is 2=burger+fries again on 2021-08-11 we loose one more = burger and this burger will stay active till 2022-08-01.

 

This is just one client, MCD but in one visual I have to show for all clients at a particular time how many companies are in which category.

 

Something like below:

I have also attached the pbix file with the message.

 

Appreciate your help.

 

Thank you

 

  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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
    )

     

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    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
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        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 ) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Alexis ðŸ˜Š You are awesome. Solution worked for me

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous  please attache the pbix