Calculate value based on unique values in different table

[Lilia]

Hello all,

I need of urgent help for some DAX calculation.  Here it is my case:

I have three tables  you could see it below

You should know that item type = show the type of the item/ Equipment or location

I would like to calculate how many pieces of equipment and location I have per inspection number. 

As you could see in above screen shot that table where the inspection number is store is not connected to location table in which you could find information what is the type of the equipment.

I will appreciate if someone could help me with ideas how I could solve that.

Br, 

[P3Tom]

Assuming I have not misspelled your table or column names, all you need is two measures (not calcualated columns) for you Location table and two measures for you Inspection table which you will find below.

In a matrix, put [InspectionID] in the Rows field well, and put both [Location Count by Inspection ID] and [Equipment Count By Inspection ID] in the Values field well .

The other two measures are for intermediate calculations and are not to be put in the matrix.

I tried to use your sample data and it was hard to tell if the formulas were working. I created a similar model with fewer row and testing showed the formulas worked.

Equipment Count=COUNTROWS ( FILTER ( Location, Location[Item type] = "Equipment" ) )

Location Count=COUNTROWS ( FILTER ( Location, Location[Item Type] = "Location" ) )

Location Count for Inspection ID=CALCULATE ( [Location Count], CROSSFILTER ( Data[LocationID], Location[LocationID], Both ) )

Equipment Count By Inspection ID=CALCULATE ( [Equipment Count], CROSSFILTER ( Data[LocationID], Location[LocationID], Both ) )

[v-shex-msft]

Hi @Lilia,

Can you please share some sample data to analysis and test?

Regards,

Xiaoxin Sheng

[Lilia]

1.  Hello @v-shex-msft
   
   
   Please find attached link to some sample data to analysis and test. https://1drv.ms/f/s!Av0-ISefEa7u4iMhcegcVqoiqiHA
   
   I would like to calculate how many pieces of equipment and locations I have per inspection id- display ID.
   Table content;
        In location table, I have a description of the location and of what item is behind this location id, is it equipment or location.
       In dates- I have location id and inspection id. Which inspection id, if it's connected to inspection table, will show the inspection number.
       In Inspection table, you could find an InspectioniD and Location ID.

[Anonymous]

Hi @Lilia

A quick question: How do you check how many equipments there is pr. inspectionID ?

An idea could be to have duplicates in your location table and then just insert inspectionID in that table. Then you could simply count amount of locations pr. inspectionID. Does that make sense?

Best,

Martin

[Lilia]

Hi @Anonymous

Could you please give me a comprehensive explanation what I should do, when you are saying"have duplicates in your location table and then just insert inspectionID in that table. Then you could simply count amount of locations pr. inspectionID. "

And Dates table contains the key indicators Location ID and Inspection ID. And then I could not connect Location table and Inspection table because there is no anything common in them. In  Location table, I have Location ID but the same is missing in inspection table.


BR

[Lilia]

Hello @Anonymous

I have an information for the type of the "Location Id" if it's an equipment or location in the column " Item type" at "Location table".

I have tried to merge Location table which contains an information what the "location ID" is (equipment or location)and "Dates Table" which has a unique key column " Inspection ID" ( which shows the inspection number ) - not working
I have done the merging between "Location table" and "Dates table" but when I try to apply the changes. it's not working.

What I need is a table that combines the information in those three tables - Inspection ID, Location and Dates table

I need the "location id", "inspection display number" and "item type" in one place. Maybe if I have a way to take only those columns and combine them into a new table.

I hope this gives more information about my issue.

Thank you for have been trying to help me.

Br,

[nickchobotar]

@Lilia

You would have to create a bridge table between your Dates Table [LocaitonID] and Location Table [LocaitonID]and create relationships between these tables. Then you can write your count measure.

BridgeTable =
SUMMARIZE (
    UNION (
        DISTINCT ( 'DatesTable'[LocaitonID] ),
        DISTINCT ( 'LocationTable'[LocaitonID] )
    ),
    [LocaitonID]
)

Nick -

[Lilia]

Hello @nickchobotar

I have tried to use your solution but I'm getting error the error below:

Am I doing something wrong ?
Br,

[nickchobotar]

@Lilia

You need to finish the  code with [LocationID]  in brackets not just Locations.  I gave you an idea and standard pattern for bridge table. I looked at the data  and it is not easy to interpret it. Could you please post an end result in a table of what exactly you need.

N -