Forum Discussion

mahenkj21's avatar
mahenkj21
Frequent Visitor
7 years ago
Solved

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

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 ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
mahenkj21
Frequent Visitor
7 years ago

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's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.