Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi, hope someone can help point out where and why this doesn't work. I have the following measure
Count Findings = CALCULATE(
COUNT(Findings[Finding ID]),
USERELATIONSHIP('Date'[Date], Findings[Date created]),
(
Findings[Category of finding] = "Major NC" ||
Findings[Category of finding] = "Minor NC" ||
Findings[Category of finding] = "Opportunity for Improvement"
)
)
I have a date table and relationship to the date created. I have multiple tables with multiple dates in each tables so multiple relationships across the whole model so need to use a date table.
When I pull this into a pivot table with date[month] and use a slicer on date [year] I get completely wrong results.
results:
results I should get
My table filtered from 1st jan 2024 showing that the results are wrong (I have no January or Feb entries!).
I have checked all dates are dates and not date time, I have made all relationships from date table to other tables inactive, tried countx, counta, filter, creating separate measures or var for the or conditions. I just cannot find a solution to give me the correct result. I'm assuming I'm missing something in the filter context but I can't figure it out.
any Help and explanation appreciated.
Solved! Go to Solution.
Thanks, in posting it sent me to other posts where I solved it. The date had been changed on the table but not in transform data where it was still a date time, also needed to add keep filters.
Thanks, in posting it sent me to other posts where I solved it. The date had been changed on the table but not in transform data where it was still a date time, also needed to add keep filters.
@Bls-80 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
25 | |
14 | |
14 | |
12 |