Hello all,
I have a report with a simple data table containing a datetime column, an ID column, and a value associated with each entry. There is a many-to-one relationship between the ID column in the data table and a Key column in a fact table. Each entry in the fact table has information associated with it such as the Unit the data comes from and what the tag is measuring. Here's a mock-up of what my data looks like:
| FACT TABLE |
| Unit | Attribute | Key Column [ 1 : * ] |
| A1 | Status | S1A1Status |
| A1 | Yield | S1A1Yield |
| A1 | Temperature | S1A1Temperature |
| A1 | Pressure | S1A1Pressure |
| A1 | Flow Rate | S1A1Flow Rate |
| A2 | Status | S1A2Status |
| A2 | Yield | S1A2Yield |
| A2 | Temperature | S1A2Temperature |
| A2 | Pressure | S1A2Pressure |
| A2 | Flow Rate | S1A2Flow Rate |
| A3 | Status | S1A3Status |
| A3 | Yield | S1A3Yield |
| A3 | Temperature | S1A3Temperature |
| A3 | Pressure | S1A3Pressure |
| A3 | Flow Rate | S1A3Flow Rate |
| A4 | Status | S1A4Status |
| A4 | Yield | S1A4Yield |
| A4 | Temperature | S1A4Temperature |
| A4 | Pressure | S1A4Pressure |
| A4 | Flow Rate | S1A4Flow Rate |
| DATA TABLE |
| DateTime | Record ID [ * : 1 ] | Value | Run Status (the column I want to make with desired result) |
| 1/1/2021 0:00 | S1A1Status | 0 | 0 |
| 1/1/2021 0:00 | S1A1Yield | 15.5 | 0 |
| 1/1/2021 0:00 | S1A1Temperature | 100 | 0 |
| 1/1/2021 0:00 | S1A1Pressure | 2 | 0 |
| 1/1/2021 0:00 | S1A1Flow Rate | 1000 | 0 |
| 1/1/2021 0:00 | S1A2Status | 1 | 1 |
| 1/1/2021 0:00 | S1A2Yield | 70 | 1 |
| 1/1/2021 0:00 | S1A2Temperature | 1000 | 1 |
| 1/1/2021 0:00 | S1A2Pressure | 15 | 1 |
| 1/1/2021 0:00 | S1A2Flow Rate | 20000 | 1 |
| 1/1/2021 6:00 | S1A1Status | 0 | 0 |
| 1/1/2021 6:00 | S1A1Yield | 10 | 0 |
| 1/1/2021 6:00 | S1A1Temperature | 75 | 0 |
| 1/1/2021 6:00 | S1A1Pressure | 1 | 0 |
| 1/1/2021 6:00 | S1A1Flow Rate | 500 | 0 |
| 1/1/2021 6:00 | S1A2Status | 1 | 1 |
| 1/1/2021 6:00 | S1A2Yield | 71 | 1 |
| 1/1/2021 6:00 | S1A2Temperature | 1200 | 1 |
| 1/1/2021 6:00 | S1A2Pressure | 14 | 1 |
| 1/1/2021 6:00 | S1A2Flow Rate | 2100 | 1 |
| 1/1/2021 12:00 | S1A1Status | 1 | 1 |
| 1/1/2021 12:00 | S1A1Yield | 68 | 1 |
| 1/1/2021 12:00 | S1A1Temperature | 900 | 1 |
| 1/1/2021 12:00 | S1A1Pressure | 10 | 1 |
| 1/1/2021 12:00 | S1A1Flow Rate | 1600 | 1 |
| 1/1/2021 12:00 | S1A2Status | 1 | 1 |
| 1/1/2021 12:00 | S1A2Yield | 70 | 1 |
| 1/1/2021 12:00 | S1A2Temperature | 1150 | 1 |
| 1/1/2021 12:00 | S1A2Pressure | 15 | 1 |
| 1/1/2021 12:00 | S1A2Flow Rate | 2010 | 1 |
There is a "Status" measurement in the fact table associated with each unit. (ex. Unit A1 Status differs from Unit A2 Status, etc.) This measurement lets me know if the unit was active for each time in the Data Table.
Here's what I want to do:
In the data table, determine if the Status tag is active ( = 1 ) or inactive ( = 0 ) for each datetime in the table.
Then, in a calculated column, assign a value of 1 or 0 to all entries with the same datetime value and related Unit assignment from the fact table.
I've run into problems getting DAX to assign the status value to entries where the associated measurement in the fact table IS NOT = "Status". I think that this is happening because I'm setting the logic up as follows:
-> assign a variable to determine the Unit (in the Fact Table) associated with the record ID (in the Data Table)
-> determine the value associated with the Status entry for each datetime (in the Date Table)
-> for all entries with the same datetime value AND the same related Unit assignment, RETURN the related Status value determined above (this is the step I'm stuck on)
I would greatly appreciate any help in figuring out how the logic would work for this dataset. I'm open to trying options in M and/or DAX (currently trying DAX due to the relationship between the tables).
Thanks!
