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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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