Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mahenkj21
Frequent Visitor

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 ImageSample Image

best regards.

1 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

7.png

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])

5.png6.png

 

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.

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

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

7.png

 

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.

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.

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

7.png

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])

5.png6.png

 

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
Community Support
Community Support

Hi @mahenkj21 

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

 

Best Regards

Maggie

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!

SampleFile1.png

Is there someone to help me out!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.