Counting Rows using 2 measures....

[Anonymous]

Due to the way my data is coming in I have had to make a number of measures. 

The first to calculate a ranking based on 2 objects in a table based upon a time. This is as follows:

Y_Planned_Sequence =

var cur_date =

    SELECTEDVALUE('ipp IPP_Daily_Report'[sales_date])

var cur_loc =

    SELECTEDVALUE('ipp IPP_Daily_Report'[location_machine])

var tmp =

    filter(

        ALL('ipp IPP_Daily_Report'),

        'ipp IPP_Daily_Report'[location_machine]= cur_loc

        && 'ipp IPP_Daily_Report'[sales_date] = cur_date)

return

    RANKX( tmp,CALCULATE(SELECTEDVALUE('ipp IPP_Daily_Report'[Actual Start Time Production])),,ASC,Dense)

This value is then needs to be compared to a target value that comes from a merged table query column this is called Target Swq

So i  get an output in a table like so

My data contains multiple values for category , sales date and machine... 

What I am looking for is a measure than counts the number of time the Plan and Target dont match, so in the example above the value for Bread on 4th March on location Machine would be 3...  as Seq 2,3,4 do not match the target sequence.

The idea is to have a frontend dashboard with the top line figures on and then other pages with the detailed data on per section.

Thanks In anticpation

Andy

 

[v-jianboli-msft]

Hi @Anonymous ,

Please try:

Measure =
COUNTROWS (
    FILTER (
        ALL ( 'ipp IPP_Daily_Report' ),
        [Target Seq]
            <> RANKX (
                FILTER (
                    ALL ( 'ipp IPP_Daily_Report' ),
                    [sales_date] = EARLIER ( 'ipp IPP_Daily_Report'[sales_date] )
                        && [location_machine] = EARLIER ( 'ipp IPP_Daily_Report'[location_machine] )
                ),
                [Actual Start Time Production],
                ,
                ASC,
                DENSE
            )
    )
)

Output:

Best Regards,

Jianbo Li

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