Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone, I need your small help. I have two facts tables: -
- One is Current stock report
- One is Min-Max report (how much stock a firm need to maintain per month.
? Need to calculate the current stock in the Min-Max table based on some parameter.
Have taken one small example to understand the data pattern:-
Min-Stock Need to Maintain | |||
Product | Size | Color | Min-Stock |
A | 2 | black, blue | 25 |
A | 3 | orange | 12 |
B | 2 | blue | 21 |
B | 3 | white, yellow | 34 |
Current Stock | |||
Product | Size | Color | CurrentStock |
A | 2 | black | 5 |
A | 3 | blue | 4 |
A | 2 | blue | 3 |
A | 3 | yellow | 4 |
A | 2 | yellow | 5 |
B | 3 | white | 7 |
B | 2 | organge | 8 |
B | 2 | blue | 5 |
B | 2 | black | 4 |
B | 3 | white | 2 |
Looking for the result as shown below:
Result | ||||
Product | Size | Color | Min-Stock | CurrentStock |
A | 2 | black, blue | 25 | 8 |
A | 3 | orange | 12 | 0 |
B | 2 | blue | 21 | 5 |
B | 3 | white, yellow | 34 | 9 |
Note:-
For product A, we need to find the color combination of both black and blue in a single cell. We have 1200 products. Need to write one dax function to fetach the corrent data. We can't bifurcation the color combination to two different row. We are fine with any ratio between black and blue.
Please anyone knows give me the solutions.
@Ruth, @amitchandak, @Tahreem24, @Greg_Deckler, @MFelix, @Pragati11, @mwegener
@mallot86 @Ruth @avisingh @Patrik
@amitchandak @Tahreem24 @Greg_Deckler @MFelix @Pragati11 @mwegener
Solved! Go to Solution.
Hi @Anonymous ,
If you want to write dax to achieve this, you can try to create this measure:
_CurrentStock =
CALCULATE (
SUM ( 'Current Stock'[CurrentStock] ),
FILTER (
'Current Stock',
'Current Stock'[Product]
IN DISTINCT ( 'Min-Stock Need to Maintain'[Product] )
&& 'Current Stock'[Size]
IN DISTINCT ( 'Min-Stock Need to Maintain'[Size] )
&& CONTAINSSTRING (
SELECTEDVALUE ( 'Min-Stock Need to Maintain'[Color] ),
'Current Stock'[Color]
)
)
) + 0
Attached the sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If you want to write dax to achieve this, you can try to create this measure:
_CurrentStock =
CALCULATE (
SUM ( 'Current Stock'[CurrentStock] ),
FILTER (
'Current Stock',
'Current Stock'[Product]
IN DISTINCT ( 'Min-Stock Need to Maintain'[Product] )
&& 'Current Stock'[Size]
IN DISTINCT ( 'Min-Stock Need to Maintain'[Size] )
&& CONTAINSSTRING (
SELECTEDVALUE ( 'Min-Stock Need to Maintain'[Color] ),
'Current Stock'[Color]
)
)
) + 0
Attached the sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |