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
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:
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
Solved! Go to 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:
Proud to be a Super User!
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:
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:
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
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.
Hi @Ksm2004,
Can you please provide a sample of your data, for example two table with some data, and the final output?
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
106 | |
93 | |
69 |
User | Count |
---|---|
167 | |
132 | |
130 | |
96 | |
91 |