Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to filter the count of rows using date column

I am very new to powerBI , I am trying to calculate the percentge of my passing devices. Below is the sample of my data.

and this is what i am trying to translate from sql to power BI

 

Declare min_date DATETIME = '2020-1-13 00:00:00'
Declare max_date DATETIME = '2020-1-14 00:00

SELECT 100 *(
(SELECT
(SELECT count (distinct SerialNumber)
FROM TTable where
(CAST( RequestTime AS DATETIME) between CAST(min_date AS DATETIME) And CAST(max_date AS DATETIME))) -
(Select count(*) as retried
from ( SELECT SerialNumber,
count (*) - SUM (CASE when status = 'P' THEN 1 ELSE 0 END) as TotalRetries
FROM TTable where
(CAST( RequestTime AS DATETIME) between CAST(min_date AS DATETIME) And CAST(max_date AS DATETIME))
Group by SerialNumber
HAVING count (*) - SUM (CASE when status = 'P' THEN 1 ELSE 0 END) > 0 ) retried ) AS Difference ))
/ ((SELECT count (distinct SerialNumber)
FROM TTable where
(CAST( RequestTime AS DATETIME) between CAST(min_date AS DATETIME) And CAST(max_date AS DATETIME))) + 0.0
) as PassingData ;

 

I have grouped the data in native query of power BI and able to get total count. I am also able to get the total count of all Pass and Fail by adding a new column, but when i  filter the date the count doesnt change. i want the count to change based on date How can i do that.  

Also how can i subtract the fail count from pass count and divide it by pass count and count should be be updated as per date

something like below :

(passcount - fail count) / pass count 

 

 

h.PNG

 
 

 

SerialNumberDateTimeStatus
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000F
G00022019-09-09T16:01:50.7030000F
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000F
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00022019-09-09T16:01:50.7030000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000F
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:12:00.2370000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000F
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:48:14.9470000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000F
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P
G00012020-01-02T12:54:39.1630000P

 

 

2 ACCEPTED SOLUTIONS
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

1) Just drag the date column to the dashboard.

2) Create those measures:

 

F = CALCULATE(COUNT(Nadiah[Status]); FILTER(Nadiah; Nadiah[Status] = "F"))
P = CALCULATE(COUNT(Nadiah[Status]); FILTER(Nadiah; Nadiah[Status] = "P"))
Result = DIVIDE([P] - [F]; [P]; 0)
 
3) Drag a table and the measures onto the table.
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@Anonymous ,Try a new column like

divide(
(countx(filter(table,table[SerialNumber] = earlier(table[SerialNumber]) && table[status]="P"),table[status])
- countx(filter(table,table[SerialNumber] = earlier(table[SerialNumber]) && table[status]="P"),table[status])),
countx(filter(table,table[SerialNumber] = earlier(table[SerialNumber]) && table[status]="P"),table[status])
)

Or a measure like this

divide(
(countx(filter(table,table[status]="P"),table[status])
- countx(filter(table, && table[status]="P"),table[status])),
countx(filter(table, table[status]="P"),table[status])
)

Anonymous
Not applicable

@amitchandak I tried the measure by making few modifications and it worked like this

divide(
(countx(filter(table,table[status]="P"),table[status])
- countx(filter(table, table[status]="F"),table[status])),
countx(filter(table, table[status]="P"),table[status])

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur 

I tried linking the two tables and it gives the data between two days like i wanted. 

Thank you all so much for your support. Really appreciate the help. Was stuck on this for 2 days and tried alot of tutorials.

You guys are magicians. Thank you 🙂

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

1) Just drag the date column to the dashboard.

2) Create those measures:

 

F = CALCULATE(COUNT(Nadiah[Status]); FILTER(Nadiah; Nadiah[Status] = "F"))
P = CALCULATE(COUNT(Nadiah[Status]); FILTER(Nadiah; Nadiah[Status] = "P"))
Result = DIVIDE([P] - [F]; [P]; 0)
 
3) Drag a table and the measures onto the table.
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Oh Awesome , Thankyou it really works.

One more question. As of now it gives the per day division.  What can i do if i want to find the division result between selected dates .

 
 

for example I am getting result at 

div result on 3rd jan 

div result on 4th jan

div result on 5th jan

 

what if i want one div result from 3rd - 5th jan

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.