cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Count unknown data with date possible?

Hi Power Community!
Known.Unknown data.PNG

so this shows the "known" data and "unknown" data (data with no delivery date or forwarder). So far so good. 

 

Now I have a date slicer. If you use the date slicer it for the last months it only shows the known data.

The reason is that the unknown data has of course no date but my boss wants to know how much unknown data we have every month not only overall....

 

Thank you for your time.

 

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @Beginner_2020 ,

 

According to my understanding, you want to display total/known/unknown data based on date slicer, right?

You could try this:

Total = COUNTROWS('Table') 
Known Data =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[DeliveryDate] <> BLANK ()
            && 'Table'[Forwarder] <> BLANK ()
    )
) + 0

 The final output looks like this:

12.23.1.1.gif

Please kindly check my pbix file here.

 

Best Regards,
Eyelyn Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

Hi @Beginner_2020 ,

 

According to my understanding, you want to display total/known/unknown data based on date slicer, right?

You could try this:

Total = COUNTROWS('Table') 
Known Data =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[DeliveryDate] <> BLANK ()
            && 'Table'[Forwarder] <> BLANK ()
    )
) + 0

 The final output looks like this:

12.23.1.1.gif

Please kindly check my pbix file here.

 

Best Regards,
Eyelyn Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@Eyelyn9 Excuse me. I had no time in the holiday season...

Thank you for your time - it worked perfectly 😊  

Super User IV
Super User IV

@Beginner_2020 ,

for unknown data use like

 unknown new = calculate([unknown], all(Date), isblank(Table[Deilvery Date]))

or

 unknown new = calculate([unknown], all(Table), isblank(Table[Deilvery Date]))

or

 unknown new = calculate([unknown], filter(all(Table[Date]), isblank(Table[Deilvery Date])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak Thanks for the help.
better but still.PNG

now the number of unknown data is constant but it still does not change with the date slicer. Do you have an idea?

@Beginner_2020  You need to have a date column for reference. What is your data source? Is there a created or modified date we can use? You may need to use the DAX function USERELATIONSHIP() to use an inactive relationship between date table and modified date to know when the unknown data was modified or created??

 

Hopefully that makes some sense? 

 

Without some date, we can't know which numbers apply to each month.


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors