cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Help in writing Dax in Tabular Model

DateDimensionIDJobCloseDatePolicyNumberBranchIDJob
201607017/1/2016 0:00A1Submission
201607027/2/2016 0:00A3Policy Change
201607027/3/2016 1:00C4Reinstatement
201607037/3/2016 1:00A5Cancellation
201607037/3/2016 2:00B7Cancellation
201607037/3/2016 3:00B8Cancellation
201607037/3/2016 4:00A6Cancellation
201607047/5/2016 0:00A9Reinstatement

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.

4 REPLIES 4
scottsen Senior Member
Senior Member

Re: Help in writing Dax in Tabular Model

Re: Help in writing Dax in Tabular Model

Friend

@scottsen

I do have SSDT.

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

Moderator v-sihou-msft
Moderator

Re: Help in writing Dax in Tabular Model

@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)

Capture2.PNG

 

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

 

Capture6.PNG

 

It can show correct result when filtered with DateDimensionID.

 

Capture7.PNG

Capture8.PNG

 

Regards,

Re: Help in writing Dax in Tabular Model

@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)