How to use SWITCH & CALCULATE to calculate from one table, by applying filter from a second table

[mtomova]

Hi All!

I am trying to create a calculated measure by using SWITCH & CALCULATE to cover multiple statuses.

The information I use is separated in two different tables, there is a connection between the tables, 

however, my CALCULATE is not returning any result.

The two tables are HH and Measures.

For one HH(Household) I can have multiple entries in the Measures table. The two tables are related by unique reference number for each Household.

Based on a SWITCH statement that is being true, if a Household Ops status = X
Then count number of referals in Measures table, if criteria met.

My Calculation returns nothing:

SWITCH(TRUE(),
FIRSTNONBLANK('HH'[Ops Status], 1) = "Allocated to Installer (Measures)",

CALCULATE(COUNT('Measure'[SURVEYMEASUREVIEW::Reference]), 'Measure'[Measure Ops Status] = "Allocated to Installer (Measures)"),

FIRSTNONBLANK('HH'[Ops Status], 1) = "Allocated to Installer (Measures)",

CALCULATE(COUNT('Measure'[SURVEYMEASUREVIEW::Reference]), 'Measure'[Measure Ops Status] = "Awaiting technical survey (Measures)"),

etc...

)

[mtomova]

Hello, @amitchandak !
Apologies for the late response!
HH table:

Reference	Ops Status
100601	Awaiting Whole House Plan
100606	Retrofit Design Approved (Household)
100608	Allocated to Installer (Household)
100613	Awaiting Whole House Plan
100621	Awaiting Inspection
100622	Allocated to Installer (Household)
100631	Allocated to Installer (Household)
100635	Allocated to Installer (Household)
100638	Retrofit Design Approved (Household)

Measures table:

Reference	Measure Ops Status
100606	Retrofit Design Approved (Measures)
100606	Retrofit Design Approved (Measures)
100606	Measure Billed
100606	Measure Billed
100606	Retrofit Design Approved (Measures)
100608	Allocated to Installer (Measures)
100608	Retrofit Design Approved (Measures)
100608	Measure Billed
100608	Measure Billed
100621	Measure Billed
100621	Measure Billed
100621	Measure Billed
100621	Measure Billed
100622	Allocated to Installer (Measures)
100622	Measure Billed
100622	Measure Billed
100631	Allocated to Installer (Measures)
100631	Allocated to Installer (Measures)
100631	Measure Billed
100631	Measure Billed
100631	Measure Billed
100631	Retrofit Design Approved (Measures)
100635	Allocated to Installer (Measures)
100635	Allocated to Installer (Measures)
100635	Measure Billed
100635	Measure Billed
100638	Measure Billed
100638	Measure Billed
100638	Retrofit Design Approved (Measures)

I am not sure if there is a way to upload these tables as files.
I could not find how to do it, apologies if the format is not user friendly!

[amitchandak]

@mtomova ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.