Forum Discussion

kirti1921's avatar
kirti1921
New Member
2 years ago

inactive relationship is not working properly.

hello everyone,

I have model having created[created date], created[Modified resolved date], calendar[date].
active relation between - created[created date], calendar[date].
inactive relation between - created[Modified resolved date], calendar[date].

Measures I've used: 

#trial1 = CALCULATE(DISTINCTCOUNT('Created Bug all'[Target ID]),FILTER('Created Bug all','Created Bug all'[Source State]="Resolved" ||
'Created Bug all'[Source State]="Closed"),USERELATIONSHIP('Calendar'[Date],'Created Bug all'[Modified Resolved Date]))

# To Be automated =
var a =IF (
    ISFILTERED ( 'MOdified date table' ),
    CALCULATE (
        DISTINCTCOUNT('Created Bug all'[Target ID]), FILTER('Created Bug all','Created Bug all'[Target ID] <> "Blank"),
        USERELATIONSHIP ( 'Created Bug all'[Modified Resolved Date], 'MOdified date table'[Date])
    ),
    CALCULATE (
        DISTINCTCOUNT('Created Bug all'[Target ID]), FILTER('Created Bug all','Created Bug all'[Target ID] <> "Blank"))
)
RETURN
//IF(ISBLANK(a),"b",a)
a

# Automated test case =
IF (
    ISFILTERED ('MOdified date table'),
    CALCULATE (
        DISTINCTCOUNT( 'Created Bug all'[Target ID] ),FILTER('Created Bug all','Created Bug all'[Test code defect bugs.State]="Resolved" || 'Created Bug all'[Test code defect bugs.State]="Closed"),
        USERELATIONSHIP ( 'Created Bug all'[Modified Resolved Date], 'MOdified date table'[Date] )
    ),
    CALCULATE (
      DISTINCTCOUNT( 'Created Bug all'[Target ID] ),FILTER('Created Bug all','Created Bug all'[Test code defect bugs.State]="Resolved"|| 'Created Bug all'[Test code defect bugs.State]="Closed"),
        USERELATIONSHIP ( 'Created Bug all'[Modified Resolved Date], 'Calendar'[Date] )
    )
)
According to the userelationship the #trail1 by #2weekofmonth chart should give 9 as a count but its giving 4.
I'm not able to figure out why is it happening, that only those target ids are getting counted which have created date and modifiedresolvedate as same.
please refrer the snapshot below for clear understanding.
here total target id to be automated are 34 for jun24 so since 9 target ids have been automated in jun24 but in week chart it is not giving the correct results. 



Data Model

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • VikasLangote's avatar
    VikasLangote
    New Member
    2 years ago

    Hi,

    Active relationships are the primary links used by Power BI to relate tables in a data model.

    Only one active relationship can exist between two tables at a time.

     

    Inactive relationships are secondary links that can be used for specific calculations. Keep it in mind these relationships only use in dax It will not work on all page or other visuals.

     

    As per I undersand the problem statement. you need same month created and resolved bugs count. if it is right then you need to change your dax. you can try below dax may it will work for you

     

    CALCULATE (
    DISTINCTCOUNT( 'Created Bug all'[Target ID] ),
    USERELATIONSHIP ( 'Created Bug all'[Modified Resolved Date], 'MOdified date table'[Date] ),
    ('Created Bug all'[Test code defect bugs.State]="Resolved" || 'Created Bug all'[Test code defect bugs.State]="Closed"),
    Month('Created Bug all'[Modified Resolved Date]) = Month('MOdified date table'[Date]),
    Year('Created Bug all'[Modified Resolved Date]) = Year('MOdified date table'[Date])
    )

     

     

    - Regards

    Vikas