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
Bls-80
Frequent Visitor

Calculate, count and use relationships giving incorrect results

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. 

Bls80_3-1726588173080.png

 


When I pull this into a pivot table with date[month] and use a slicer on date [year] I get completely wrong results.
results:

Bls80_0-1726587625940.png

results I should get

Bls80_1-1726587651788.png

My table filtered from 1st jan 2024 showing that the results are wrong (I have no January or Feb entries!).

Bls80_2-1726587883003.png

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.

1 ACCEPTED SOLUTION
Bls-80
Frequent Visitor

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. 

View solution in original post

2 REPLIES 2
Bls-80
Frequent Visitor

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. 

Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors