Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
best regards.
Solved! Go to 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.
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.
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
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.
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!
Is there someone to help me out!!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |