DAX measure with Conditions

[mehaboob557]

Hi All,

Please check this pbix file.

My concept was to get YES or NO H&A status. I have used the two measures(you can find the in the pbix file count and H&A) to achieve that.

Now i need to calculate with the below scenario,

1.If H&A have YES in all main line items in 2nd table.. i need status GOOD_READY. 

2. If any one line item is NO(i.e if 4 line items are YES and one line item is NO) then it should be NOT_READY. 

If you see my measure COUNT , there i have filtered the values 

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

From these filtered values i should get the value if Count > 1  is YES and else NO.  Which i have done in H&A measure as below.

H&A = IF([Count] >= 2, "Yes", "No")

From this YES and NO i have to set the value GOODS_READY or GOODS_NOT_READY.

for ex:

1)

table1 visual

salesorder_no |  main line item | count | H&A

dso/....x05              some name      2         YES

dso/....x05              some name      5         YES

then status to be set AS GOODS_READY

 tabke2 visual

salesorder_no |  party_name    | status

dso/....x05              some name      GOODS_READY

2)

table1 visual

salesorder_no |  main line item | count | H&A

dso/....x07              some name      2         YES

dso/....x07              some name      1         NO

dso/....x07              some name      7         YES

then status to be set AS GOODS_NOT_READY because one of the line item is NO in above table visual

 tabke2 visual

salesorder_no |  party_name    | status

dso/....x07              some name      GOODS_NOT_READY

Please share your ideas or updated pbix file. I am in stuck with this from past 5 days

Thanks in advance

[Eric_Zhang]

@mehaboob557

You can try a measure as below.

Measure = 
var tbl =SUMMARIZE(sourcing,sourcing[main_line_item],"CNT",[Count])
RETURN
IF(MINX(tbl,[CNT])=1,"NOT_READY","GOOD_READY")

By the way, do note to mask sensitve data when sharing a pbix file in case of any security risk.

[mehaboob557]

Hi Thanks,

Yeah it's just a dummy data i have shared.

But, it should only show oone line item instead of multiple items.

If you see my measure COUNT , there i have filtered the values 

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

From these filtered values i should get the value if Count > 1  is YES and else NO.  Which i have done in H&A measure as below.

H&A = IF([Count] >= 2, "Yes", "No")

From this YES and NO i have to set the value GOODS_READY or GOODS_NOT_READY.

for ex:

1)

table1 visual

salesorder_no |  main line item | count | H&A

dso/....x05              some name      2         YES

dso/....x05              some name      5         YES

then status to be set AS GOODS_READY

 tabke2 visual

salesorder_no |  party_name    | status

dso/....x05              some name      GOODS_READY

2)

table1 visual

salesorder_no |  main line item | count | H&A

dso/....x07              some name      2         YES

dso/....x07              some name      1         NO

dso/....x07              some name      7         YES

then status to be set AS GOODS_NOT_READY because one of the line item is NO in above table visual

 tabke2 visual

salesorder_no |  party_name    | status

dso/....x07              some name      GOODS_NOT_READY

This what i am looking for @Eric_Zhang

Please share your ideas or updated pbix file. I am in stuck with this from past 5 days

Thanks in advance

[mehaboob557 · Accepted solution]

I have used measure to make it happen.

Measure = IF(SUMX(ADDCOLUMNS(CROSSJOIN(VALUES(details[salesorder_no]),VALUES(sourcing[main_line_item])),"HA",IF(ISBLANK('details'[Count]),BLANK(),IF([Count]>1,BLANK(),1))),[HA])>0,"GOODS NOT READY","GOODS READY")