Measures: Approved and Distinct

[Yrstruly2021]

Please assist?

I would like to calculate the services rejected. I have total services requested and services approved. I tried to subtract the two, i am getting errors.

Also would like to: 

Measure = DISTINCTCOUNT(Activitypoint)

I  want to see the Approved for this distinct calculation?

https://drive.google.com/file/d/1iI5CmvZMKetaVV7Ugwc9pBXqQqFIuAxu/view?usp=sharing 

[Anonymous · Accepted solution]

Hi @Yrstruly2021 ,

Request 1:

You will need to create columns to get day, week and month for the ids.

Then create measures as below by using ALLEXCEPT() function.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

measure1 = calculate(distinctcount([id]),ALLEXCEPT('table',[day]))

measure2 = calculate(distinctcount([id]),ALLEXCEPT('table',[week]))

measure3 = calculate(distinctcount([id]),ALLEXCEPT('table',[month]))
Request 2:

Use FILTER() function to add the conditions to formula.

https://docs.microsoft.com/en-us/dax/filter-function-dax 

approved = calculate(distinctcount([id]),FILTER(allselected('table'),[servicerequeststate_id] = 6

rejected = calculate(distinctcount([id]),FILTER(allselected('table'),[servicerequeststate_id] = 5

Request 3:

activity point live = calculate(distinctcount([activitypoint]),FILTER(allselected('table'),[servicerequeststate_id] = 6

Best Reagrds,

Jay

[Yrstruly2021]

Please give dax equivalent of  "countd( if [Is Approved]=1 then ([Activitypoint])end"?

[Greg_Deckler]

@Yrstruly2021 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Yrstruly2021]

CREATE TABLE [dbo].[ServiceRequest_3](
[id] [int] NOT NULL,
[activitypoint] [float] NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL,
[fbocode] [nvarchar](255) NULL,
[ordertype] [float] NULL,
[ordercategory] [float] NULL,
[requestorid] [nvarchar](255) NULL,
[quotation] [float] NULL,
[address] [nvarchar](255) NULL,
[inspectiontype] [float] NULL,
[analysistype] [float] NULL,
[certificationtype] [float] NULL,
[volume] [float] NULL,
[targetregion] [float] NULL,
[targetcountry] [float] NULL,
[servicerequeststate_id] [int] NOT NULL,
[submittedforapproval] [float] NULL,
[IsApproved] [float] NULL,
[datecreated] [datetime] NULL,
[datemodified] [datetime] NULL,
[dateapproved] [datetime] NULL,
[modifiedby] [float] NULL,
[approvedby] [float] NULL,
[acceptedterms] [float] NULL,
[structurecode] [int] NOT NULL,
[rejectionreason] [nvarchar](255) NULL,
[iscancelled] [float] NULL,