Need to Compare Requirement vs Availability

[Anonymous]

Hello Dax Experts,

I have 3 tables -

Table 1 : MachineDetails (Machine , Serial No, Mandatory Repair, Mandatory Repair Hours Type, Location). This Table gives details of machine. Serial No of machine will be unique. Mandatory Repair gives information that mandatory repair is required or not. Mandatory Repair Hours type give information that it is 500 Hours Repair or 1000 Hours Repair and Location is location of that machine.

Machine	Serial No	Mandatory Repair	Mandatory Repair Hours Type	Location
A	100	Y	500 Hours	X
A	101	N		X
A	102	N		X
A	103	Y	1000 Hours	X
B	501	Y	500 Hours	Z
B	502	Y	500 Hours	Z
B	503	N		Z
B	504	N		Z

Table 2:  PartsRequired (Machine, Mandatory Repair Hours Type, Part No, Qty). This table gives information about the parts required to do Mandatory Repair machine wise. For each machine quantity of parts for 500/1000 hours repair can be obtained from here.

Machine	Mandatory Repair Hours Type	Part No	Qty
A	500 Hours	ABC123	1
A	500 Hours	ABC345	1
A	500 Hours	ABC678	1
A	500 Hours	ABC91011	1
A	500 Hours	ABC887	1
A	1000 Hours	ABC123	1
A	1000 Hours	ABC345	1
A	1000 Hours	ABC765	1
A	1000 Hours	ABC445	1
A	1000 Hours	ABC340	1
B	500 Hours	ABC123	1
B	500 Hours	PQR789	1
B	1000 Hours	ABC123	1
B	1000 Hours	PQR990	1

Table 3: DealerStock ( Dealer, Location, Part No, Stock). This table gives infromation about dealer's stock of parts. At one location only one dealer will be there.

Dealer	Location	Part No	Stock
A1	X	ABC123	2
A1	X	ABC345	2
A1	X	ABC678	0
A1	X	ABC91011	1
A1	X	ABC887	1
A1	X	ABC765	1
A1	X	ABC445	20
A1	X	ABC340	14
A1	X	PQR789	4
A1	X	PQR990	67
A2	Y	ABC123	3
A2	Y	ABC345	7
A2	Y	ABC678	2
A2	Y	ABC91011	7
A2	Y	ABC887	5
A2	Y	ABC765	7
A2	Y	ABC445	9
A2	Y	ABC340	4
A2	Y	PQR789	2
A2	Y	PQR990	8

Slicers on reports will be Machine and location.

I want to Compare Requirement of Service vs Availability against Mandatory Service Hour Type(from table 1)

If a user selects location X in the report the requirement will be 2 (one for 500 Hours and one for 1000 Hours). But availaibilty will be 1 as Part - ABC678 is out of stock for location X.

Expected output for Location X and Machine A will be something like-

Request you to please help here.

@Greg_Deckler @parry2k @PaulDBrown @AllisonKennedy @amitchandak @AlexisOlson 

Thanks in Advance!

[Anonymous]

Hello @Greg_Deckler ,

Thank you very much for inputs but it is not giving result as expected.

I need to take the count of machines serial number when Service is possible but measure is returning 1 for 500 Hours when Machine A is selected.

For machine A type, two services are required (For serial no 100 and 103).

But for serial no 100 service is not possible as one of the part required(ABC678) is out of stock.

So when user selects Machine A. 1 count should come as availability against 1000 hours.

Measure output is showing 1 against 500 Hours.

If possible can I please have PBIX also in which you have solved it? It will be great help and I will make sure that I am not doing anything worng.

Thank you very much in Advance!

Kind Regards.

[Greg_Deckler]

@Anonymous The measure I created was for the "Available" cell in your image. I didn't actually test it in a PBIX file so the fact that it didn't have any syntax errors is a miracle.

[Greg_Deckler]

@Anonymous OK, so a little hard to put the puzzle pieces together on this and I assume you meant ABC678 is out of stock since that is 0. I'm thinking someting along the lines of:

Available Measure =
  VAR __Machine = MAX('Table1'[Machine])
  VAR __LocationType = MAX('Table1'[TypeLocation])
  VAR __MandatoryRepairHours = MAX('Table1'[Mandatory Repair Hours])
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        ADDCOLUMNS(
          FILTER(ALL('Table2'),[Machine]=__Machine && [MandatoryRepairHours]=__MandatoryRepairHours),
          "__LocationType",__LocationType
        ),
        "__Stock",
          VAR __PartNo = [Part No]
        RETURN
          MAXX(FILTER('Table3',[Part No]=__PartNo && [Location]=__LocationType),[Stock])
      ),
      "__Diff" = IF([Qty] <= [__Stock],1,0)
    )
  VAR __Count = COUNTROWS(FILTER(__Table, [__Diff] = 0))
RETURN
  IF(ISBLANK(__Count) || [__Count] = 0,0,1)