topic Return values for one column only if they match criteria from another column in another table in DAX Commands and Tips

Return values for one column only if they match criteria from another column in another table

Anonymous — Fri, 29 Oct 2021 16:03:58 GMT

Hello,

I'd like to know which lots ran on the day they were supposed to (tables shown below), and I'm not sure how to go about it. I think I need to create another table.... I imagine the table would have 3 columns: Lot Number (ONLY those lots that were run on the date they were scheduled), Production Start Date (the date the lot actually ran), and the Lot Type. The biggest issue I'm having is the measure needed for the Lot Number column to return only the relevant values. Also, multiple lots run on the same day

The end goal is to create card visualizations counting the number of lots based on type. "count of type A lots that actually ran on the date they were supposed to" "count of type B lots that actually ran on the date they were supposed to", etc. So I feel like with the table explained above I could easily use count measures to create these cards?

Schedule data (when the lot was scheduled to run)

Schedule date	Lot number	Lot Type
10252021	12345	A
10252021	23456	B

Production data (when the lot actually ran)

Production start date	Lot number	Lot type
10252021	12345	A
10262021	23456	B

If it helps, I am using this measure to count the lots that were run on the day they were scheduled. Now I need to know which lots were counted in this measure (Thanks to VahidDM on my other post):

Actual # of Lots =

VAR _A =

FILTER (

ADDCOLUMNS (

production_data,

"Sche-Date",

LOOKUPVALUE (

schedule_data[schedule_date],

schedule_data[lot_no], production_data[lot_no]

)

[production_start_date] = [Sche-Date]

)

RETURN

COUNTROWS ( _A )

Re: Return values for one column only if they match criteria from another column in another table

AlexisOlson — Fri, 29 Oct 2021 20:20:30 GMT

It sounds like you want to add the Lot type as another filter condition.

Type A lots run on proper day = VAR _A = FILTER ( ADDCOLUMNS ( production_data, "Sche-Date", LOOKUPVALUE ( schedule_data[schedule_date], schedule_data[lot_no], production_data[lot_no] ) ), [production_start_date] = [Sche-Date] && [Lot type] = "A" ) RETURN COUNTROWS ( _A )

Re: Return values for one column only if they match criteria from another column in another table

Anonymous — Sun, 31 Oct 2021 15:49:31 GMT

Thank you for your reply! I don't really need to count them anymore, I really do think I need a table in order to manipulate the data in the ways needed for the rest of my dashboard. This is helpful for the cards though!

Re: Return values for one column only if they match criteria from another column in another table

v-robertq-msft — Wed, 03 Nov 2021 09:25:24 GMT

Hi, Anonymous

According to your DAX formula and your sample data, I can’t find the data field [schedule_date] in your sample data and I have no idea how to create the sample test data to achieve your needs. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.