topic Re: Measures: Approved and Distinct in DAX Commands and Tips

Measures: Approved and Distinct

Yrstruly2021 — Sat, 05 Feb 2022 12:32:34 GMT

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

Re: Measures: Approved and Distinct

Greg_Deckler — Sat, 05 Feb 2022 16:24:51 GMT

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.

Re: Measures: Approved and Distinct

Yrstruly2021 — Sat, 05 Feb 2022 21:41:51 GMT

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

Re: Measures: Approved and Distinct

Yrstruly2021 — Sat, 05 Feb 2022 21:44:41 GMT

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,

Re: Measures: Approved and Distinct

Anonymous — Wed, 16 Feb 2022 08:58:44 GMT

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