Forum Discussion

Giavo's avatar
Giavo
Icon for Helper III rankHelper III
7 years ago
Solved

DAX: SELECTEDVALUE with Dates

Hello all,   INTRO: i have a Relational model with 3 tables: 1)Project Milestones table containing Project Codes and 4 dates per each project (Date1, Date2, Date3, Date4) 2)Projects Table cont...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Giavo I am just looking for sample data not actual data, but anyway I guess you are looking for this..

     

    Test122Count = 
    VAR _SelectedYear = SELECTEDVALUE(_DimDate[Year])
    VAR _Temp = FILTER(Test122,
     ((YEAR(Test122[Date1])<=_SelectedYear || YEAR(Test122[Date2])<=_SelectedYear) && (YEAR(Test122[Date3])>=_SelectedYear || YEAR(Test122[Date4])>=_SelectedYear)))
    VAR _Temp1 = IF(COUNTROWS(_Temp)>0,1,0)
    RETURN SUMX(_Temp,_Temp1)

  • richbenmintz's avatar
    richbenmintz
    7 years ago

    Hi Giavo,

     

    I think the following formula should do what you expect, essentially it will count the rows on the 'Project Milestones' table where the conditions are met based on the selected value

     

    Active in X =
VAR SelectedYear=SELECTEDVALUE('Calendar'[Year])
RETURN 
Calculate(CountRows('Project Milestones'), FILTER('Project Milestones', (YEAR(Date 1)<=SelectedYear || YEAR(Date 2)<=SelectedYear) &&
(YEAR(Date 3)>= SelectedYear || YEAR(Date 4)>=SelectedYear)))
Giavo's avatar
Giavo
Icon for Helper III rankHelper III
7 years ago

Thank you for your help, it is useful but howewer it's not working 100%

If i put 2 columns in a table i have this result,
Project,          Active
Project1      Not Active
Project2      Active
Project3      Active

So apparently it works. But when i want to count per each year the number of Active Project it doesn't work anymore and i see this result :
Year , Count Projects

1999      199

2000      199

2001      199

2002      199

2003      199

I'm wondernig why ? Per each project it tells if the project is Active or not, but if i count the number of projets per year it doesn't work anymore

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago
Giavo Could you please post the sample data and expected output, which will help us to understand the scenario in more detail.
  • Giavo's avatar
    Giavo
    Icon for Helper III rankHelper III
    7 years ago

    Unfortunately i can't share the dataset, But basically i would like to count per each year the number of Active Projects so:

    Year   Count Active Projets
    1999          21

    2000          39

    2001         10

    2002          2

    • PattemManohar's avatar
      PattemManohar
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Giavo I am just looking for sample data not actual data, but anyway I guess you are looking for this..

       

      Test122Count = 
      VAR _SelectedYear = SELECTEDVALUE(_DimDate[Year])
      VAR _Temp = FILTER(Test122,
       ((YEAR(Test122[Date1])<=_SelectedYear || YEAR(Test122[Date2])<=_SelectedYear) && (YEAR(Test122[Date3])>=_SelectedYear || YEAR(Test122[Date4])>=_SelectedYear)))
      VAR _Temp1 = IF(COUNTROWS(_Temp)>0,1,0)
      RETURN SUMX(_Temp,_Temp1)

      • Giavo's avatar
        Giavo
        Icon for Helper III rankHelper III
        7 years ago

        I did exactly the same thing, still not working. Per each year i have the same number of Projects (total of 296), even if i select a certain year i still have the same number (296). if i don't select a year then i still see the same 296 per each year.

        I'm using Power BI Report Report Server Desktop August 2018.

        Is it possible that you have connected the DimDate table to your Test table ? or there's no relationship at all ? In my case they are not connected an looks like because of this there's no reaction if i filter the year, i mean:

        If i don't filter any year, then i see per each year 296 projects, but if i select only one year i still see 296 projects per that year. The measure is not adapting to the year selection