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 All,
I need your help badly.
Here is my pbix file
i have two tables details and sourcing. in the pbix file i exported the data into csv and created a new pbix file which i have shared now.
I need a value YES or NO. To get that i have to check the below scenario.
1. i have to look the data only for the "item_type" = "Product" and "inv_category" should be "Customized Cabinet','Customized Crockery unit', 'Customized Dresser Unit', 'Customized Entertainment unit', 'Customized Kitchen', 'Customized Pooja Unit', 'Customized Shoe Rack', 'Customized Study Unit', 'Customized Table', 'Customized Utility', 'Customized Vanity Unit', 'Customized Wardrobe" from details table
2. for the above point condition, i will get some rows, there i have to check the count of "main_line_item" from sourcing table
3. If Count is >= 2 the value should be YES else NO
Please Help me how can i achieve this. I am in stuck with this from past 3 days.
Thank you in advance
Solved! Go to Solution.
As I understand your description you could try the following:
First change the relationship between Bridge and Sourcing to a both way Cross filter direction: Go the Relationships tab and double click the relationship between Bridge and Sourcing and set Cross Filter direction to Both and enable "Apply security filter in both directions"
Then try to add the following measures. You might have to change the ; to , depending on where you are in the world.
Count = CALCULATE( COUNTROWS( sourcing ); details[item_type] = "Product"; ( details[inv_category] = "Customized Cabinet" || details[inv_category] = "Customized Crockery unit" || details[inv_category] = "Customized Dresser Unit" || details[inv_category] = "Customized Entertainment unit" || details[inv_category] = "Customized Kitchen" || details[inv_category] = "Customized Pooja Unit" || details[inv_category] = "Customized Shoe Rack" || details[inv_category] = "Customized Study Unit" || details[inv_category] = "Customized Table" || details[inv_category] = "Customized Utility" || details[inv_category] = "Customized Vanity Unit" || details[inv_category] = "Customized Wardrobe" ) ) H&A Status = IF([Count] >= 2; "Yes"; "No")
Could you please create an example of how you want you final result to look like... this might affect the way the DAX formula for the calculation.
Hi @sdjensen,
Thanks for the response.
The sample output will be like below
sales Order no | main line item | count of Main line item | party Name | batch NO| H&A status
DSO/...xxxx01 | abc | 4 | xyz | some no | YES
DSO/...xxxx01 | mno | 4 | xyz | some no | YES
DSO/...xxxx01 | def | 1 | xyz | some no | NO
One more thing i want to let u know is main_line_item count should be from sourcing table
As I understand your description you could try the following:
First change the relationship between Bridge and Sourcing to a both way Cross filter direction: Go the Relationships tab and double click the relationship between Bridge and Sourcing and set Cross Filter direction to Both and enable "Apply security filter in both directions"
Then try to add the following measures. You might have to change the ; to , depending on where you are in the world.
Count = CALCULATE( COUNTROWS( sourcing ); details[item_type] = "Product"; ( details[inv_category] = "Customized Cabinet" || details[inv_category] = "Customized Crockery unit" || details[inv_category] = "Customized Dresser Unit" || details[inv_category] = "Customized Entertainment unit" || details[inv_category] = "Customized Kitchen" || details[inv_category] = "Customized Pooja Unit" || details[inv_category] = "Customized Shoe Rack" || details[inv_category] = "Customized Study Unit" || details[inv_category] = "Customized Table" || details[inv_category] = "Customized Utility" || details[inv_category] = "Customized Vanity Unit" || details[inv_category] = "Customized Wardrobe" ) ) H&A Status = IF([Count] >= 2; "Yes"; "No")
Hi @sdjensen,
Everything working fine. But,
If i remove H&A from table, i am getting only 4 items(which is correct) but when i add H&A, it is showing all the line items .
I need only 4 items with YES and NO status .
I have done teh both relationship then i have created the two measures as below
Measure 1:
Count =
CALCULATE(
COUNTROWS( 'gubbi_ods erp_so_sourcing' ),
'gubbi_ods erp_so_details'[item_type] = "Product",
( 'gubbi_ods erp_so_details'[inv_category] = "Customized Cabinet"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Crockery unit"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Dresser Unit"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Entertainment unit"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Kitchen"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Pooja Unit"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Shoe Rack"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Study Unit"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Table"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Utility"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Vanity Unit"
|| 'gubbi_ods erp_so_details'[inv_category] = "Customized Wardrobe"
)
)
Measure 2:
H&A = IF([Count] >= 2, "Yes", "No")
(whoch
If you only want rows that has a value in Count you can go to the visualization filters and on the count measure set a filter to be differenct from 0
Oh Working Fine 🙂
One more calculation i have to do.
If any one line item is NO. The Status(new measure) to be NO else YES.
This Status will be added in another table visual
How can i Achieve this. Please suggest
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |