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"
        )
    )
)

     

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).

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        PatrickByGecko  - I think there is a problem with "ors" (||) and "ands" (&&). Please use the parentheses as they were in the measure I sent. Mixing "Ands" and "Ors" can produce unpredictable/problmatic logic. 

        The logic is:

        Category = "<Some Category>"

        AND

        (

          Worker Date = Date

          OR

          (

            Worker Date is Prior to Begin Date

            AND

            Date = Begin Date

          )

        )