Forum Discussion

Martin0011's avatar
Martin0011
Frequent Visitor
4 years ago

Handle inactive relationship - cross table

Hi,

I have 3 tables : 

 

Date[Date]

 

Request [Request Number , Request Date]

Request NumberRequest Date
131/12/2021
201/01/2022
301/01/2022
401/01/2022
501/01/2022

RequestLog [Request Number, Request Log Status, Request Date]

Request NumberReqeuset Log StatusRequest Date
1REJECT01/01/2022
2APPROVE01/01/2022
3REJECT01/01/2022
4REJECT01/01/2022
5APPROVE01/01/2022

 

There is an active relationship with  Date[Date] & Request [Request Log Date] [1-*]

There is an Inactive relationship with  Date[Date] & RequestLog [Request Log Date] [1-*]

There is an active relationship with Request [Request Number] & RequestLog [Request Number]

 

The goald is to get a Measure with : 

 

 

 

 

 

 

# Rejected Request = 
    CALCULATE(
        COUNT(Request[Request Number]),

        FILTER(RequestLog[Request Number],   
           (RequestLog[Request Status]="REJECT"
        )
    )

 

 

 

 

 

 

The issue is that I need all Reject, in January. But as the relation with Date and Reqeust is active, it remove the request # as it is created in December. So, in January, it will not be counted even if the ReqeustLog was in January. 

 

How to handle this in the measure ? 

I tried with USERELATIONSHIP or REMOVEFILTERS

 

Thanks for the advises , 

M.

 
 
Need Help

4 Replies

  • mh2587's avatar
    mh2587
    Super User
    4 years ago
    # Rejected Request = 
    CALCULATE(
        COUNT(Request[Request Number]),

        FILTER(RequestLog[Request Number],   
           (RequestLog[Request Status]="REJECT"
        ), USERELATIONSHIP (Request[Request Number],RequestLog[Request Number] )
    )
  • Martin0011's avatar
    Martin0011
    Frequent Visitor
    4 years ago

     

    Here is the relation : 

     

    I tried this : 

    Rejected Request = 
    CALCULATE(
        DISTINCTCOUNT(Fac_ZVM_REQLOG[Vendor Request Number]),
        FILTER(Fac_ZVM_REQLOG,Fac_ZVM_REQLOG[Function Code] = "REJECT"),
        USERELATIONSHIP(Dim_Date[Date],Fac_ZVM_REQLOG[Reporting Period])
    )

     

    It keeps filtering the Date on the REQUEST table - request outside February are not reporting in February but the log is well Rejected in Jan. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Martin0011 ,

    Please refer to my pbix file to see if it helps you.

    Create a measure.

    Rejected =
CALCULATE (
    COUNT ( Request[Request Number] ),
    FILTER (
        ALL ( RequestLog ),
        ( RequestLog[Reqeuset Log Status] = "REJECT"
            && RequestLog[Request Date].[MonthNo] = 1 )
    )
)

    If I have misunderstood your meaning, please provide your desired output with a screenshot.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.