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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
achurk
Frequent Visitor

CountIf-Type Function in DAX

Schedule table

Schedule date

Lot number

10252021

12345

10252021

23456

 

Production table

Production start date

Lot number

10252021

12345

10262021

23456

 

 

We want to count the number of lot numbers in the production table only if the start date for the production table matches the date in the schedule date table.

 

For example, we would only count 1 for Lot number 12345 because the production start date matches the date in the schedule date table. The lot number actually ran on the day that it was scheduled.

 

So far we've tried this:

CountIf = COUNTROWS(FILTER(production_data,production_data[new_plot_no]=schedule_date,schedule_date[new_slot_no]))

 

Based on this starting point from class:

 

Countrows(filter(schedule data, lot_no_new(#####)=

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

Hi @achurk 

 

Try this measure:

Measure = 
VAR _A =
    FILTER (
        ADDCOLUMNS (
            production_data,
            "Sche-Date",
                LOOKUPVALUE (
                    schedule_date[Schedule date],
                    schedule_date[Lot number], production_data[Lot number]
                )
        ),
        [Production start date] = [Sche-Date]
    )
RETURN
    COUNTROWS ( _A )

 

Output:

VahidDM_0-1635209507028.png

 

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

Appreciate your Kudos!!

 

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

Hi @achurk 

 

Try this measure:

Measure = 
VAR _A =
    FILTER (
        ADDCOLUMNS (
            production_data,
            "Sche-Date",
                LOOKUPVALUE (
                    schedule_date[Schedule date],
                    schedule_date[Lot number], production_data[Lot number]
                )
        ),
        [Production start date] = [Sche-Date]
    )
RETURN
    COUNTROWS ( _A )

 

Output:

VahidDM_0-1635209507028.png

 

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

Appreciate your Kudos!!

 

Hello again! Is there a way to extract those lot numbers that were counted? So instead of counting the lots I would be able to see which lots were run?

 

Here is another column of the data I'm using. 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

 

Schedule table

Schedule date

Lot number

Lot Type

10252021

12345

A

10252021

23456

B

 

Production table

Production start date

Lot number

Lot type

10252021

12345

A

10262021

23456

B

This is great, thank you so much!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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