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
mehaboob557
Resolver IV
Resolver IV

Need to get value using DAX

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

1 ACCEPTED 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")
/sdjensen

View solution in original post

7 REPLIES 7
sdjensen
Solution Sage
Solution Sage

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.

/sdjensen

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")
/sdjensen

Hi @sdjensen,

 

Everything working fine. But,

 

test.PNG

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

/sdjensen

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

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.