Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How could I take like columns from three different tables (lets say DivisionCode) and all possible Month/year combinations and build a dynamic table from them.
This way we could build slicers that included the DivisionCodes from each of the tables and themselves available based on month and year slicers. So lets say we have a HistoricalEmployee File with only active employees in each month/year. So if Bob worked here a year, he's have twelve entries, one for each month/year combination within that Division. If he moves to a new division then he'd have 11 entries if you filtered his one division and one entry in the new divsion, etc.
Now If Nancy worked here a year but was fired in her 12th month she'd have eleven entries for each month/year combination except her last month. And one entry in a Seperations file that just lists all possible separations and their separation dates.
There is also an OpenPostions file each month/year and some OpenPostions carry over month to month and even year to year.
Now the OpenPositions table may have positions in a new Division so until those people are hired that Division wouldn't be on the HistoricalEmployee table. Consequently if all employees in a Division were Separated one month that combination of division/Month/Year would not exist in the HistoricalEmployee table. etc.
So how do I go about making one large dynamic table that pulled for all possible date/division combinations regardless of what table they were in so that if a Division is added to any table its available to report on for all tables.
Does that make sense?
Best regards,
~Don
Sample data and output would be great, but probably you are going to use GENERATE or GENERATEALL. So something like:
Table 2 = VAR __table = GENERATEALL(VALUES('Table22'[Column1]),VALUES('Table22'[Column2])) VAR __table1 = GENERATEALL(__table,VALUES('Table22'[Column3])) RETURN __table1
and then a column like:
Column = [Column1] & [Column2] & [Column3]
See Table22 and Table 2 in attached.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |