Forum Discussion

PatrickByGecko's avatar
PatrickByGecko
Helper 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"
        )
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

PatrickByGecko  - Try the following:

1. Create a relationship between the 2 tables, if there is not yet one.

2. Create a measure for Males and a measure for Females. For example:

Females = 
var _maxdate = max(Dates[Date])
return COUNTROWS(
    FILTER(
        ALL(Workers),
        Workers[DateEntry] <= _maxdate && Workers[Sex] = "female"
    )
)

3. Create a table visual, and add the date from the Dates table, along with your 2 new measures.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
  • PatrickByGecko's avatar
    PatrickByGecko
    Helper 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
        Helper 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.