Assistance with writing a measure

[OrenArbiv]

Hey Community members,

I have the following two tables and columns:
1. Locations
With the following columns: LocationID and LocationTypeID
2. Stock
With the following columns: Date and LocationID

The LocationID is a primary key of the location table and therefore a foreign key in the Stock table.

I have tried to write a measure that does the following: 
determines if a LocationID of LocationType = 4 appears more than once on the same date in the Stock table, and then returns 1 if true and 0 otherwise.
One more constrain - the column LocationTypeID is a Text that needs to be converted to an integer.

I have tried doing it for hours but all my attepts failed. 

Thank you!

[v-tangjie-msft · Accepted solution]

Hi @OrenArbiv ,

Thanks to @_AAndrade  and @Joe_Barry  for their quick replies.
I have some other thoughts to add:

(1) This is my test data.  

Date = CALENDAR(MIN('Stock'[Date]),MAX('Stock'[Date]))

(2) We can create a calculated column on Table Locations.

LocationTypeID_int = CONVERT([LocationTypeID],INTEGER)

(3) We can create a measure. 

Measure = 
var _table1= CALCULATETABLE(VALUES('Locations'[ LocationID]),'Locations'[LocationTypeID_int]=4)
var _count=COUNTROWS(FILTER(ALLSELECTED('Stock'),'Stock'[Date]=MAX('Date'[Date]) &&'Stock'[LocationID] in _table1))
VAR _Result = IF( _count > 1, 1,0)
RETURN
    _Result

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[_AAndrade]

Hi @OrenArbiv,

I'm using this two tables with the information that you provide above:

First is the Location table and the second one is the stock table.
In order to change the data type of the LocationTypeID you can use the formula on the power query:

Table.TransformColumnTypes(Source,{{"LocationTypeID", Int64.Type}})

After that I use this DAX formula:

Num LocType4 = 
VAR _NumRows = CALCULATE( COUNT(T_Stock[Date]), T_Locations[LocationTypeID]=4 )
VAR _Result = IF( _NumRows > 1, 1)
RETURN
    _Result

The final result is this:

If you need any help, please let me know.

[OrenArbiv]

Hey!

Firstly, thanks for the quick answer.

I made a small changed but used your code:

Num LocType4 = 
VAR _NumRows = CALCULATE(
    COUNT(Stock[Date]), CONVERT(Location[LocationTypeID],INTEGER) = 4)
VAR _Result = IF( _NumRows > 1, 1)
RETURN
    _Result

Unfortunately it still doesn't work for me.

Is there a chance the issue is with the relationships?

The relationships are the following:
1. Dates -> Stock (1:*)
2. Location -> Stock (1:*)

[_AAndrade]

I'm not understanding where is the issue. If you change the 

IF( _NumRows > 1, 1)

to 

IF( _NumRows > 1, _NumRows)

What would be the output?

[OrenArbiv]

Here it is:

LocationID 1.A.02.1.4 has in general 7 entries in the Stock table.

Generally, both the Stock and Locations table have additional columns but I didn't mention them as I believe its irrelevant. 

[Joe_Barry]

Hi @OrenArbiv 

I presume you created a one to mayn relationship from the Locations to the Stock table on the LocationID? Do you also have a Date table with the relationship to the Date column in Stock?

If you do, create this basic measure to count the location

Locations = COUNT('Stock'[LocationID])

Then this to filter for Type = 4

LocationType 4 =
CALCULATE([Locations], 
  KEEPFILTERS('Locations'[LocationTypeID] = 4))

Add the Date from your Date table in a Table or Matrix Vissual and then add the 2nd measure,

Thanks

Joe

If you found my answer helpful and it solved your issue, please accept as solution