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...
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")
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")
  • mehaboob557's avatar
    mehaboob557
    Resolver III
    8 years ago

    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 

    • sdjensen's avatar
      sdjensen
      Solution Sage
      8 years ago

      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

      • mehaboob557's avatar
        mehaboob557
        Resolver III
        8 years ago

        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