Need help on filter a fact table based on Product specification.

[mahenkj21]

Hi,

I need help on filter a fact table based on Product specification.

I have a fact file in which test characteristics are stored of products. Each product has about 30 test characteristics. I want to
filter this table based on another table where I store product specifications one or several sets of test characteristics (30 or less
depending needs) min and max values (2 columns). Data should be filtered based on passing range of min and max.

I have tried to insert an image of sample data.

Hope I am able to explain the need.

Sample Image

best regards.

[v-juanli-msft]

Hi @mahenkj21 

Create measures in "tblTestItems" table

a1_max =
CALCULATE (
    MAX ( tblspecification[Max] ),
    FILTER ( ALLSELECTED ( tblspecification ), [TestItem] = "Attribute1" )
)


a1_min =
CALCULATE (
    MIN ( tblspecification[Min] ),
    FILTER ( ALLSELECTED ( tblspecification ), [TestItem] = "Attribute1" )
)


a2_max =
CALCULATE (
    MAX ( tblspecification[Max] ),
    FILTER ( ALLSELECTED ( tblspecification ), [TestItem] = "Attribute2" )
)


a2_min =
CALCULATE (
    MIN ( tblspecification[Min] ),
    FILTER ( ALLSELECTED ( tblspecification ), [TestItem] = "Attribute2" )
)

Measure =
VAR a1 =
    MAX ( tblTestItems[Attribute1] )
VAR a2 =
    MAX ( tblTestItems[Attribute2] )
RETURN
    IF (
        [a1_min] = BLANK ()
            || [a1_max] = BLANK ()
            || [a2_min] = BLANK ()
            || [a2_max] = BLANK (),
        IF (
            ( a1 >= [a1_min]
                && a1 <= [a1_max] )
                || ( a2 >= [a2_min]
                && a2 <= [a2_max] ),
            1,
            0
        ),
        IF (
            a1 >= [a1_min]
                && a1 <= [a1_max]
                && a2 >= [a2_min]
                && a2 <= [a2_max],
            1,
            0
        )
    )

Please refer to my pbix to see more details.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahenkj21]

Hi,

Thanks for making this working.

Though, as I mention in my first post, I have such 30 attributes and going with your solution I would have to create many calculated columns of min, max (maybe 60). I wanted to make it simpler, i.e. a product specification table which keeps filtering fact table whatever attributes I keep adding.

I can accept a Power query solution as well or make some changes in the model so as to get this done.

Hope I am clear enough.

best regards.

[v-juanli-msft · Accepted solution]

Hi @mahenkj21 

Open edit queries, select "Attribute1" and "Attribute2" in the "tblTestItems" table, select "unpivot columns"

apply &&close

create a new table

link = VALUES(tblspecification[TestItem])

then create relationship as below

Create measures in "tblTestItems"

Max =
VAR max1 =
    CALCULATE (
        MAX ( tblspecification[Max] ),
        ALLSELECTED ( tblspecification ),
        FILTER ( ALL ( tblTestItems ), [Attribute] = MAX ( [Attribute] ) )
    )
RETURN
    IF ( max1 <> BLANK (), max1, MAX ( tblTestItems[Value] ) )


Min =
VAR min1 =
    CALCULATE (
        MIN ( tblspecification[Min] ),
        ALLSELECTED ( tblspecification ),
        FILTER ( ALL ( tblTestItems ), [Attribute] = MAX ( [Attribute] ) )
    )
RETURN
    IF ( min1 <> BLANK (), min1, MAX ( tblTestItems[Value] ) )


flag1 =
IF (
    MAX ( tblTestItems[Value] ) >= [Min]
        && MAX ( tblTestItems[Value] ) <= [Max],
    1,
    0
)


flag2 = SUMX(FILTER(ALL(tblTestItems),[Product]=MAX([Product])),[flag1])

Please see more details in my pbix.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @mahenkj21 

Could you show me what's the relationship between the two tables?

Best Regards

Maggie

[mahenkj21]

Hi Maggie,

Please see little more informative screenshot. 

What I am trying to accomplish is, have a dimension table for specification which will filter tblSpecification (one to many), of which row items of field TestItem will filter Products in tblTestItems. There is no relationship as of now in tblTestItems and tblSpecification.

Is it possible this way!

[mahenkj21]

[mahenkj21]

Is there someone to help me out!!