Reply
Member
Posts: 117
Registered: ‎08-01-2017
Accepted Solution

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


Accepted Solutions
Senior Member
Posts: 337
Registered: ‎01-14-2016

Re: Need to get value using DAX

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


All Replies
Senior Member
Posts: 337
Registered: ‎01-14-2016

Re: Need to get value using DAX

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
Member
Posts: 117
Registered: ‎08-01-2017

Re: Need to get value using DAX

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

 

 

 

Senior Member
Posts: 337
Registered: ‎01-14-2016

Re: Need to get value using DAX

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
Member
Posts: 117
Registered: ‎08-01-2017

Re: Need to get value using DAX

One more thing i want to let u know is main_line_item count should be from sourcing table

Member
Posts: 117
Registered: ‎08-01-2017

Re: Need to get value using DAX

[ Edited ]

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 

Senior Member
Posts: 337
Registered: ‎01-14-2016

Re: Need to get value using DAX

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
Highlighted
Member
Posts: 117
Registered: ‎08-01-2017

Re: Need to get value using DAX

[ Edited ]

Oh Working Fine Smiley Happy

 

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