Counting disctinct rows with filter of values and not contain values

[RZ89]

Hi,

I want to set up a reporting station Measure.

It works depends of the amout of the data sheet.

If the datasheet contains values > 07 in the column Station_ID for a BASIS_SERIAL_number, the measure counts nothing.
If the datasheet contains no values > 07 then it calculates correct.

What do I have to change in the measure to fix that?

The measure should count every distict basis serial number that have a value 05, but no value 07 in the column Stadion ID

Thanks for supporting!

Measure: 

MP05 bis MP07 = CALCULATE(DISTINCTCOUNT(ORDER_REPORTING_STATION[BASIS_SERIAL_NUMBER]),FILTER(ORDER_REPORTING_STATION,'ORDER_REPORTING_STATION'[STATION_ID] = "05"),FILTER(ORDER_REPORTING_STATION,NOT(CONTAINS(ORDER_REPORTING_STATION,ORDER_REPORTING_STATION[STATION_ID], "07"))))

 

[v-zhangti]

Hi, @RZ89 

You can try the following formula.

MP05 bis MP07 =
CALCULATE (
    DISTINCTCOUNT ( ORDER_REPORTING_STATION[BASIS_SERIAL_NUMBER] ),
    FILTER (
        ORDER_REPORTING_STATION,
        'ORDER_REPORTING_STATION'[STATION_ID] = "05"
            && 'ORDER_REPORTING_STATION'[STATION_ID] <> "07"
    )
)

Best Regards,

Community Support Team _Charlotte

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

[RZ89]

Unfortunately it doest work correct. 

In this prefiltered example the measure between MP05 and MMP07 does not count the correct amount of order "4" beacaus of one order with reporting station ID 07.

I think the measure must calculate the calculation for each individual order and if the condition is met and outputs the value 1 and then makes the sum over it. Unfortunately I am not able to do that -.-

[RZ89]

Hi v-zhangti,

thank you for that hint, unfortunately it gives me wrong calculation.
I am not sure if I had not explain my needed measure enough.

In my example it should count only POI-001099601 and POI-001100204 because there is no Station ID 07. additionaly I show other calculations that I need based on that logic