USERELATIONSHIP and FILTER

[Roland74]

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 i get the right result (4) :

Test_ROWS =

    CALCULATE(

            COUNTROWS(factPostedHourLine),

            USERELATIONSHIP(factPostedHourLine[WorkingDateID], 'Date'[DateID]),

            FILTER(Reason, Reason[ReasonCode] = "PRJ_REISWW")

            )

But i need to count the rows with an amount > 0.5, so i added another filter:

Test_ROWS_High =

    CALCULATE(

            COUNTROWS(factPostedHourLine),

            USERELATIONSHIP(factPostedHourLine[WorkingDateID], 'Date'[DateID]),

            FILTER(Reason, Reason[ReasonCode] = "PRJ_REISWW"),

            FILTER(factPostedHourLine, factPostedHourLine[Hours_Working_Date]>0.50)

            )

But whatever i've tried, i always get (Blank) as a result.

I guess it has something to do with the USERELATIONSHIP?

What am i doing wrong and why doesn't is work.

[Anonymous]

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.

[johnt75 · Accepted solution]

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

[Roland74]

Hi johnt75, This works, thanx.

Don't know why (yet), but i found the article and wil 'deep dive' in it 😆