Forum Discussion
Helper VMeasures: Approved and Distinct
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:
I want to see the Approved for this distinct calculation?
https://drive.google.com/file/d/1iI5CmvZMKetaVV7Ugwc9pBXqQqFIuAxu/view?usp=sharing
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
4 Replies
- Greg_Deckler
Community Champion
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.
Yrstruly2021CREATE 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,
- Yrstruly2021
Please give dax equivalent of "countd( if [Is Approved]=1 then ([Activitypoint])end"?
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
