Forum Discussion

Roland74's avatar
Roland74
Icon for Helper I rankHelper I
1 year ago
Solved

USERELATIONSHIP and FILTER

Hi all, Needs some help, because i don't know why the next formula doesn't work. I've got a table with the next result:   When i count the rows with reason PRJ_REISWW with the next formula ...
  • johnt75's avatar
    johnt75
    1 year ago

    Don't filter entire tables, just filter the columns you need. You can use

    Test_ROWS_High =
CALCULATE (
    COUNTROWS ( factPostedHourLine ),
    USERELATIONSHIP ( factPostedHourLine[WorkingDateID], 'Date'[DateID] ),
    Reason[ReasonCode] = "PRJ_REISWW",
    factPostedHourLine[Hours_Working_Date] > 0.50
)

    For detailed explanations of why its not working search https://www.sqlbi.com  for "expanded tables".

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Roland74 ,

 

I think you can try johnt75's workaround. If it works, please mark his reply as solution.

If not, I think your issue is based on your data model, please share a sample file to us and show us the solution you want.

Here I will give you some advice. According your screenshot, I can see [Hours_Working_Date] column in your table visual. I think you can try IF() function to update your measure.

Test_ROWS_High =
VAR _COUNT =
    CALCULATE (
        COUNTROWS ( factPostedHourLine ),
        USERELATIONSHIP ( factPostedHourLine[WorkingDateID], 'Date'[DateID] ),
        FILTER ( Reason, Reason[ReasonCode] = "PRJ_REISWW" )
    )
RETURN
    IF ( MAX ( 'TableName in Table VSIUAL'[Hours_Working_Date] ) > 0.50, _COUNT, BLANK () )

 

Best Regards,
Rico Zhou

 

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