Forum Discussion

mehaboob557's avatar
mehaboob557
Resolver III
8 years ago
Solved

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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • sdjensen's avatar
    sdjensen
    8 years ago

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

7 Replies

  • sdjensen's avatar
    sdjensen
    Solution Sage
    8 years ago

    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.

    • mehaboob557's avatar
      mehaboob557
      Resolver III
      8 years ago

      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

       

       

       

      • sdjensen's avatar
        sdjensen
        Solution Sage
        8 years ago

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