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 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.

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.

7 Replies

  • 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.

    • mahenkj21's avatar
      mahenkj21
      Frequent Visitor
      7 years ago

      Hi Maggie,

       

      Please see little more informative screenshot. 

       

      Sample 2

       

      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!