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

      )

    )

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

      I did put some "(" the best I can, but only the first row is completed I mean when date=_DateBegin.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        PatrickByGecko  - Can you please check some particular dates that are known to have data and let me know whether they have data for the Measure?