Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter Rows by Status Record for All Related Datetime and Unit Entries (Sample Tables Included)

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
UnitAttributeKey Column [ 1 : * ]
A1StatusS1A1Status
A1YieldS1A1Yield
A1TemperatureS1A1Temperature
A1PressureS1A1Pressure
A1Flow RateS1A1Flow Rate
A2StatusS1A2Status
A2YieldS1A2Yield
A2TemperatureS1A2Temperature
A2PressureS1A2Pressure
A2Flow RateS1A2Flow Rate
A3StatusS1A3Status
A3YieldS1A3Yield
A3TemperatureS1A3Temperature
A3PressureS1A3Pressure
A3Flow RateS1A3Flow Rate
A4StatusS1A4Status
A4YieldS1A4Yield
A4TemperatureS1A4Temperature
A4PressureS1A4Pressure
A4Flow RateS1A4Flow Rate

 

DATA TABLE
DateTimeRecord ID [ * : 1 ]Value

Run Status

(the column I want to make with desired result)

1/1/2021 0:00S1A1Status00
1/1/2021 0:00S1A1Yield15.50
1/1/2021 0:00S1A1Temperature1000
1/1/2021 0:00S1A1Pressure20
1/1/2021 0:00S1A1Flow Rate10000
1/1/2021 0:00S1A2Status11
1/1/2021 0:00S1A2Yield701
1/1/2021 0:00S1A2Temperature10001
1/1/2021 0:00S1A2Pressure151
1/1/2021 0:00S1A2Flow Rate200001
1/1/2021 6:00S1A1Status00
1/1/2021 6:00S1A1Yield100
1/1/2021 6:00S1A1Temperature750
1/1/2021 6:00S1A1Pressure10
1/1/2021 6:00S1A1Flow Rate5000
1/1/2021 6:00S1A2Status11
1/1/2021 6:00S1A2Yield711
1/1/2021 6:00S1A2Temperature12001
1/1/2021 6:00S1A2Pressure141
1/1/2021 6:00S1A2Flow Rate21001
1/1/2021 12:00S1A1Status11
1/1/2021 12:00S1A1Yield681
1/1/2021 12:00S1A1Temperature9001
1/1/2021 12:00S1A1Pressure101
1/1/2021 12:00S1A1Flow Rate16001
1/1/2021 12:00S1A2Status11
1/1/2021 12:00S1A2Yield701
1/1/2021 12:00S1A2Temperature11501
1/1/2021 12:00S1A2Pressure151
1/1/2021 12:00S1A2Flow Rate20101

 

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! 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I tried a couple of formulas and got something to work using the LOOKUPVALUE function. This is what that function looks like (modified to fit with the mock data I've included in this thread).

Run Status =
VAR
relatedDate = 'Data Table'[DateTime]
VAR
relatedUnit = RELATED( 'Fact Table'[Unit] )
VAR
runStatus =
LOOKUPVALUE(
    'Data Table'[Value],
    'Fact Table'[Attribute], "Status",
    'Fact Table'[Unit], relatedUnit,
    'Data Table'[DateTime], relatedDate
)
RETURN
IF( runStatus = 1,
    1,
    0
)
 
Please note that this is added as a calculated column used for the purpose of filtering my dataset (not sure if / how it could be converted to a measure for other applications).
 
Hope this helps out anyone with a similar problem!

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

I tried a couple of formulas and got something to work using the LOOKUPVALUE function. This is what that function looks like (modified to fit with the mock data I've included in this thread).

Run Status =
VAR
relatedDate = 'Data Table'[DateTime]
VAR
relatedUnit = RELATED( 'Fact Table'[Unit] )
VAR
runStatus =
LOOKUPVALUE(
    'Data Table'[Value],
    'Fact Table'[Attribute], "Status",
    'Fact Table'[Unit], relatedUnit,
    'Data Table'[DateTime], relatedDate
)
RETURN
IF( runStatus = 1,
    1,
    0
)
 
Please note that this is added as a calculated column used for the purpose of filtering my dataset (not sure if / how it could be converted to a measure for other applications).
 
Hope this helps out anyone with a similar problem!
lbendlin
Super User
Super User

Your FACT TABLE isn't. It is a dimension table.

 

From the attached example, what are you trying to achieve next?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.