problem with Filter

[Ratax]

I have 2 tables.

productionItem table:

production Item table

And ProductionStepResultID table:

they are linked with a relationship between ProductionItemID.

what i need is to calculate the number of production items that meet a certain criteria from the result table.

i need to exclude all item where the FKproductionstepID is 1071 and FALSE

but include the items where FKproductionstepID is 26 and FALSE

What i got so far is:

Calculate(

   count(ProductionItem[ProductionItemID]);

   ProductionItem[Productionitemtypeid] = 5 ;

   ProductionStepResult[FKproductionstepID] = 1071 && ProductionStepResult[ResultsIsOk] = TRUE;

   ProductionStepResult[FKproductionstepID] = 26 && ProductionStepResult[ResultsIsOk] = FALSE)

can you help?

[rsaprano · Accepted solution]

Hi @Ratax 

I think the best way to do apply specific criteria for filtering depending on row values is to add a calculated column (ideally in the query editor though otherwise in DAX) to specify whether or not it should be included and then pass it in as a filter in a simple measure.

So I tried recreating the data model from the (limited) screenshots you attached - PBIX file available here.

The calculated column just specifies the various conditions e.g. on the StepResult table:

Row to Include =
VAR ProductionStepID = ProductionStepResult[FKProductionStepld]
VAR ResultISOK =
    ProductionStepResult[ResultIsOK] = TRUE ()
RETURN
    SWITCH (
        TRUE (),
        ProductionStepID = 1071
            && NOT ( ResultISOK ), "N",
        ProductionStepID = 26
            && NOT ( ResultISOK ), "Y",
        "N/A"
    )

and then a measure to apply the conditions you specified could be something like:

No of Production Items =
VAR ProductionTypeIDFilter =
    CALCULATETABLE ( ProductionItem, ProductionItem[FKProductionItemTypeID] = 5 )
VAR ProductionStepResultFilter =
    CALCULATETABLE (
        ProductionStepResult,
        ProductionStepResult[Row to Include] = "Y"
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( ProductionStepResult[FKProductionItemID] ),
        ProductionTypeIDFilter,
        ProductionStepResultFilter
    )

Note that from the data the cardinality appears to be 1:many between ProductionItem and ProductionStepResult so your measure needs to be of a column in ProductionStepResult (The fact table); filters flow from the 1 to the many side but not the other way around unless bi-directional filters are enabled (Which is not advised!).

Hopefully that gives you something to get started with but if this sample doesn't match your data feel free to upload one that does

[Ratax]

@rsaprano 

I was a bit to quick to mark it as solved.

I need to count the productions item where 1071 is TRUE and 26 is FALSE.

Right the measure only evaluates the 26=FALSE, The Y in the calculated column.

How can i make the measure count when both a N and Y is present for each production item?

How can i revert the NOT function for the Step = 1071?

[rsaprano]

Hi @Ratax 

For a measure that needs to specify filter conditions, you can do this directly in the measure e.g.:

No of Production Items v2 =
VAR ProductionTypeIDFilter =
    CALCULATETABLE ( ProductionItem, ProductionItem[FKProductionItemTypeID] = 5 )
VAR ProductionStepResultFilter =
    FILTER (
        ProductionStepResult,
        ( ProductionStepResult[FKProductionStepld] = 1071
            && ProductionStepResult[ResultIsOK] )
            || ( ProductionStepResult[FKProductionStepld] = 26
            && NOT ( ProductionStepResult[ResultIsOK] ) )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( ProductionStepResult[FKProductionItemID] ),
        ProductionTypeIDFilter,
        ProductionStepResultFilter
    )

&& represents an 'AND' and || an 'or'

I was only suggesting going down the route of a calcualted column if there are lots of different combinations and you can define it on a row-by-row basis; otherwise putting the filter conditions in a measure is more performant and generally better.

[Ratax]

I've tried it out, and it works as intended

Thanks 🙂