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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ksm2004
New Member

Count calculation

Hi all

 

I'm quite new to Power BI and currently stuck with row count based on slicer value.

 

I have Document table with main columns: CustomerID, Date Made and Date Finalised.

Date table was added as:

Date Finalised Table = CALENDAR(FIRSTDATE('document'[Date Finalised]),LASTDATE('document'[Date Finalised]))

 

On my page I have date range Slicer based on Date Finalised Table and Table visual.

 

I'd like to show finalised and not finalised document count:

Finalised: Date Finalised is not empty and is between Slicer range

Not Finalised: Date Finalised is empty or Date Finalised is later than Slicer max date.

 

In Table visual I'd like to see something like:

Docs Finalised           35

Docs Not Finalised    27

 

External filters will be applied to Document as well.

 

Please help 

Regards

1 ACCEPTED SOLUTION

Hi @Ksm2004,

Here is my solution:

Finalised = COUNT(Document[Date Finalised])

Not Finalised = 
VAR _MaxDate = MAX('Date Finalised Table'[Date] )
VAR _AfterMaxDate= 
CALCULATE(
    [Finalised],
    FILTER(
        ALL(Document),
        Document[Date Finalised] >_MaxDate
        )
)
VAR _BlankDate =
    CALCULATE( COUNTBLANK(Document[Date Finalised]), ALL(Document))

VAR _Result = _AfterMaxDate + _BlankDate
RETURN
_Result

Final output:

_AAndrade_0-1713270941046.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Ksm2004
New Member

Please find a sample file:
https://drive.google.com/file/d/1Ei8dW8aCG7pyMN1NZA7I-y9dY5y232CG/view?usp=sharing

 

With Slicer range set 1-30/03/2024 and data as:

Ksm2004_0-1713269538344.png

result should be:

Finalised: 4

Not Finaalised: 6

 

Thanks 

Hi @Ksm2004,

Here is my solution:

Finalised = COUNT(Document[Date Finalised])

Not Finalised = 
VAR _MaxDate = MAX('Date Finalised Table'[Date] )
VAR _AfterMaxDate= 
CALCULATE(
    [Finalised],
    FILTER(
        ALL(Document),
        Document[Date Finalised] >_MaxDate
        )
)
VAR _BlankDate =
    CALCULATE( COUNTBLANK(Document[Date Finalised]), ALL(Document))

VAR _Result = _AfterMaxDate + _BlankDate
RETURN
_Result

Final output:

_AAndrade_0-1713270941046.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Thanks a lot! Works as expected.

 

Could you give some comments on the code please.

@Ksm2004 

See if this comments can help you to understand the logic I'm using:

 

Not Finalised = 
VAR _MaxDate = MAX('Date Finalised Table'[Date] ) -- Find the Max Date of the context
VAR _AfterMaxDate= 
CALCULATE(
    [Finalised], -- Same as COUNT(Document[Date Finalised])
    FILTER(
        ALL(Document), -- Remove all filter on this table
        Document[Date Finalised] >_MaxDate -- Give me only the rows where date finalised is bigger than the max date of the slicer
        )
)
VAR _BlankDate =
    CALCULATE( COUNTBLANK(Document[Date Finalised]), ALL(Document)) -- count all blank rows of the column "Date Finalised" and do this calculation of all rows of the document table (I'm using again ALL function to remove any filter)

VAR _Result = _AfterMaxDate + _BlankDate
RETURN
_Result

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Thanks for the explanation!

 

I'm trying to transfer measures to a real project. It's using dataverse as a backend and Direct Query model.

If I try to slide the Slicer, all values (Finalised and Not Finalised )  becomes blank.

Any advice on how to address it please.

 

Ksm2004_0-1713279551994.png

 

 

_AAndrade
Super User
Super User

Hi @Ksm2004,

Can you please provide a sample of your data, for example two table with some data, and the final output?





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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