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 Friends,
I'm not able to achieve the result I want it's too difficult to get.
Expected Output: Check UNITID 1st winthin the same BOMID, then only check highest BOMQTY
1. 2nd line FILTRATION_HIGHPERCENT return 1 is correct
Because within the same BOMID, you do not find TIN or PAIL in UNITID. So, u get the highest BOMQTY
2. 6th line FILTRATION_HIGHPERCENT return 1 is correct
Because within the same BOMID, you find TIN or PAIL in UNITID. So, u ignore the highest BOMQTY
3. The rest return all 0.
Solved! Go to Solution.
Hi @rainchong7401 ,
Please try:
FILTRATION_HIGHPERCENT =
VAR _a =
MAXX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _b =
MINX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _c =
(
CONTAINS (
FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
'Table'[UNITID], "PAIL"
)
|| (
CONTAINS (
FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
'Table'[UNITID], "TIN"
)
)
)
RETURN
SWITCH ( TRUE (), _c && [BOMQTY] = _b, 1, NOT ( _c ) && [BOMQTY] = _a, 1, 0 )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rainchong7401 ,
Please try:
FILTRATION_HIGHPERCENT =
VAR _a =
MAXX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _b =
MINX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _c =
(
CONTAINS (
FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
'Table'[UNITID], "PAIL"
)
|| (
CONTAINS (
FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
'Table'[UNITID], "TIN"
)
)
)
RETURN
SWITCH ( TRUE (), _c && [BOMQTY] = _b, 1, NOT ( _c ) && [BOMQTY] = _a, 1, 0 )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @rainchong7401
would do you want to create 3 table or calculated Column in table Using mention your query ?
Hi @rainchong7401 ,
Not really getting what is the result you want to achieve, the examples you give you only have TIN and PAIL in a single row so you can get it has the value you need?
Try the following code:
Check =
VAR _BOMVALUES =
CONCATENATEX (
FILTER ( ALL ( 'Table'[BOMID],'Table'[BOMQTY], 'Table'[UNITID] ), 'Table'[BOMID] = EARLIER ( 'Table'[BOMID] ) ),
'Table'[BOMQTY] & 'Table'[UNITID],
"|"
)
RETURN
IF (
(
CONTAINSSTRING ( _BOMVALUES, "1TIN" )
&& 'Table'[UNITID] = "TIN"
&& 'Table'[BOMQTY] = 1
)
|| (
CONTAINSSTRING ( _BOMVALUES, "1PAIL" )
&& 'Table'[UNITID] = "PAIL"
&& 'Table'[BOMQTY] = 1
),
1,
IF (
CONTAINSSTRING ( _BOMVALUES, "1TIN" ) || CONTAINSSTRING ( _BOMVALUES, "1PAIL" ),
0,
IF (
MAXX (
FILTER ( ALL ( 'Table' ), 'Table'[BOMID] = EARLIER ( 'Table'[BOMID] ) ),
'Table'[BOMQTY]
) = 'Table'[BOMQTY],
1,
0
)
)
)
Has you can see below I have an additonal column with 1 and 0
I also added two new lines that have pail and tin but diferent from 1.
If this is not what you need can you please share some more insights on the request.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Felix,
I'm doing this in Data,
So, var is not usable or acceptable.
below picture seems wrong already. the output.
Or you may do like this:
Combine: 1st and 2nd condition as IF STATEMENT
Seprate: 3rd condition as another STATEMENT
Hi @rainchong7401 ,
Why do you refer that you cannot use VAR? Do you mean that you are doing this on Power Query?
The use of DAX accepts the use of VAR (variables) for the calculation bo be reused and you don't have to write it again and again.
Regarding the calculation I have missread the information though there was a need for the quanity = 1 redo the calculation to:
Check =
VAR _BOMVALUES =
CONCATENATEX (
FILTER (
ALL ( 'Table'[BOMID], 'Table'[BOMQTY], 'Table'[UNITID] ),
'Table'[BOMID] = EARLIER ( 'Table'[BOMID] )
),
'Table'[BOMQTY],
"|"
)
RETURN
IF (
(
CONTAINSSTRING ( _BOMVALUES, "TIN" )
&& 'Table'[UNITID] = "TIN"
)
|| (
CONTAINSSTRING ( _BOMVALUES, "PAIL" )
&& 'Table'[UNITID] = "PAIL"
),
1,
IF (
MAXX (
FILTER ( ALL ( 'Table' ), 'Table'[BOMID] = EARLIER ( 'Table'[BOMID] ) ),
'Table'[BOMQTY]
) = 'Table'[BOMQTY],
1,
0
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Felix,
Below picture is what I want to achieve.
Expected Output: Check UNITID 1st winthin the same BOMID, then only check highest BOMQTY
1. 2nd line FILTRATION_HIGHPERCENT return 1 is correct
Because within the same BOMID, you do not find TIN or PAIL in UNITID. So, u get the highest BOMQTY
2. 6th line FILTRATION_HIGHPERCENT return 1 is correct
Because within the same BOMID, you find TIN or PAIL in UNITID. So, u ignore the highest BOMQTY
3. The rest return all 0.
Hi @rainchong7401,
Is my new formula the TINPAIL column on your example?
Can you share the formula you are using just to see if there is some adjustment that your model may need.
On my tests it appears to be correct but can have some other issue.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@rainchong7401
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |