cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luis22
New Member

How to identify a date that doesn't exist in a fact table ?

I am trying to solve a issue with dates, let me explain:

 

I have a calendar date and a fact table (checking table) where people check your entry, so the only records that exists is when the person check if the person don't check the record don't exists, the questions is, how can I identify the dates that aren't in the fact table.  

 

 

example of fact table: in this example employee 'A' didn't check on 08/17/20 and Employee 'C'  08/18/20. 

Employeedate
A08/16/20
A08/18/20
A08/19/20
B08/16/20
B08/17/20
B08/18/20
C08/16/20
C08/17/20
C08/19/20

Note: My calendar table has a relation with the column date. fact table (Date) -> Calendar table (Date)

 

I will appreciat if  you can help me with this issue

 

Thank you

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

4 REPLIES 4
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/
Luis22
New Member

With isempty works, I put a 0.

 

Now I am no t able to count the 0s, do you have some idea. it is not counting because is a result of a measure.

 

Thanks CNENFRNL!!

v-shex-msft
Community Support
Community Support

Hi @Luis22,

I'd like to suggest you use except function to compare between fact table date and calendar date, then you can simply get these not include date values list.

EXCEPT – DAX Guide
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
CNENFRNL
Community Champion
Community Champion

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!