Forum Discussion

Ksm2004's avatar
Ksm2004
New Member
2 years ago
Solved

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 a...
Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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's avatar
_AAndrade
Resident Rockstar
2 years ago

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

 

Ksm2004's avatar
Ksm2004
New Member
2 years ago

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.