Help in writing Dax in Tabular Model

[vickyprudhvi]

DateDimensionID	JobCloseDate	PolicyNumber	BranchID	Job
20160701	7/1/2016 0:00	A	1	Submission
20160702	7/2/2016 0:00	A	3	Policy Change
20160702	7/3/2016 1:00	C	4	Reinstatement
20160703	7/3/2016 1:00	A	5	Cancellation
20160703	7/3/2016 2:00	B	7	Cancellation
20160703	7/3/2016 3:00	B	8	Cancellation
20160703	7/3/2016 4:00	A	6	Cancellation
20160704	7/5/2016 0:00	A	9	Reinstatement

Hi Friends,

I am trying to write a measure in tabular which gets me count of all the policies whose latest Job is "Cancellation"

latest Job is based on JobCloseDate.

For example

IN Power BI Desktop If I have a slicer on Td_Date[CalendarDate](It is connected to this table using DateDimensionID)

If I select 07/04/2016 on the slicer then the totalCount should be 1

If I select 07/04/2016 on the slicer then the totalCount should be 2

I was able to write the dax in Dax Studio but not able to put it in tabular model.

Kindly help me out on this.

[Anonymous]

You need to install "SSDT"

https://msdn.microsoft.com/en-us/library/mt204009.aspx

[vickyprudhvi]

Friend

@Anonymous

I do have SSDT.

I want to know how to write the measure for anove condition.

[v-sihou-msft]

@vickyprudhvi

According to your description, you want to count policies if the Job on LatestCloseDate is "Cancellation" on DateDimensionID wise. Right?

In this scenario, you can create a calcualted column to determine if current row can be counted as 1 with formula below:

Column = IF(
CALCULATE(MAX(Table4[JobCloseDate]),ALLEXCEPT(Table4,Table4[PolicyNumber],Table4[DateDimensionID]))=Table4[JobCloseDate] 
&& Table4[Job]="Cancellation",1,0)

Then you just need to create a measure to sum this column.

It can show correct result when filtered with DateDimensionID.

Regards,

[vickyprudhvi]

@v-sihou-msft

Thank you for ur detail reply.

What i would like to see is

on 07/04/2016 it should show me 2

and not 1

if I select a 07/04/2016. It should count all the policies from the start to that specific date(which is 07/04/2016)