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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ABookbin
Frequent Visitor

Connecting Data From Different Files for Met/Not Met Conditions

Hi, 

 

I would like to count the Met/Not Met category I have developed for transit times and lead times. One complication is that these categories are on different excel files. 

 

Here's images of the model:

ABookbin_0-1626189917809.png

The file 'Ocean Shipment Tracker' has individual shipment data, including transit times, whereas 'Ocean LT' has planning lead times (named 85% Confidence here) according to lane name. The two files are connected using lane names. (This means there are multiple transit times associated with each lane name, but only one planning lead time associated with each lane name.)

 

Here's the code I am using right now (I am new to DAX and PowerBI, so if there is a more efficient way of doing this, please let me know!):

Ocean Met = 

Var a =

SUM('Ocean Shipment Tracking'[Actual transit time])

Var b =

SUM('Ocean LT'[85% Confidence])

Return

IF(a<b, "Met","Not Met")

This allows me to add a column in a table, but I can't figure out how to get a count of "Met" shipments:

ABookbin_1-1626190581730.png

(the third column 'Recommended Planning LT' is the '85% Confidence')

 

When I try to put this into a card, I get "Not Met". Under 'Fields', there is no option for 'Count'. 

ABookbin_2-1626190699001.pngABookbin_3-1626190785508.png

 

Any help on counting Met/Not Met would be greatly appreciated.

 

Thank you!

 

1 REPLY 1
lbendlin
Super User
Super User

That data model looks suspicious.  Please familiarize yourself with the concept of "fact" and "dimension" tables, and how they should be linked. Usually you do not want to directly connect two fact tables, and especially not via a M:M bidirectional connection.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors