Forum Discussion

PatrickByGecko's avatar
PatrickByGecko
Icon for Helper V rankHelper V
6 years ago
Solved

Creating a new table from 2 others with a calculation

Hello I want to create a new table from two others. I have one table of DATES with one column Dates (from 1/1/2019 to 31/12/2050 for instance) and an other one with WORKERS information (Name, Sex, ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    PatrickByGecko  - You could create the following Calculated Table in DAX:

    New = 
ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "male"
        )
    ),
   "Nbr Female", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "female"
        )
    )
)

     

PatrickByGecko's avatar
PatrickByGecko
Icon for Helper V rankHelper V
6 years ago

Sorry but It is not what I want.  Here is more details

 Table DATE

  • 1/1/2019
  • 2/1/2019
  • 3/1/2019
  • etc..

Table WORKER (Name, Sex, DateEntry)

  • JOHN; MALE; 1/1/2019
  • BOB; MALE; 3/1/2019
  • JANE; 2/1/2019
  • HUGH; 3/1/2019

Table NEW (Date, Nbr Male, Nbr Female)

  • 1/1/2019; 1;0
  • 2/1/2019; 1;1
  • 3/1/2019; 3;1
Anonymous's avatar
Anonymous
Not applicable
6 years ago

PatrickByGecko  - You could create the following Calculated Table in DAX:

New = 
ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "male"
        )
    ),
   "Nbr Female", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "female"
        )
    )
)

 

  • PatrickByGecko's avatar
    PatrickByGecko
    Icon for Helper V rankHelper V
    6 years ago

    Thanks a lot.

    I have a last question.

    I count now only when the DATE[date] match with the WORKER[DateEntry] so as to have the exact entries for each days (and not the sum of entries until each days). I tried and it worked fine.

     

    But my table DATES begin at 1/1/2019 and somme WORKERS[DateEntry] is from 2009, 2010 etc.. ( I do not want to change my table DATES)

    Is it possible to "put somewhere a if" so as to run different only when the current date is 1/1/2019 of my table  DATE this way =>

    New = 
ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            if(Dates[date]=value("1/1/2019"); Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "male"; Workers[DateEntry] = Dates[Date] && Workers[Sex] = "male")

    Because that does not work.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      PatrickByGecko  - Try this one:

      New 2 = 
var _CalendarBegin = DATE(2019,1,1)
return ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[Sex] = "male" && (Workers[DateEntry] = Dates[Date] || (Dates[Date] = _CalendarBegin && Workers[DateEntry] < _CalendarBegin))
        )
    ),
    "Nbr Female", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[Sex] = "female" && (Workers[DateEntry] = Dates[Date] || (Dates[Date] = _CalendarBegin && Workers[DateEntry] < _CalendarBegin))
        )
    )
)
      • PatrickByGecko's avatar
        PatrickByGecko
        Icon for Helper V rankHelper V
        6 years ago

        As you can see,  with this script I loose the first range of Dates (1/1/2019) and all counters are empty (have a look at the very bottom of this screen).