Forum Discussion

Roland74's avatar
Roland74
Helper 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 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.
 

 

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

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    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".

    • Roland74's avatar
      Roland74
      Helper I
      1 year ago

      Hi johnt75, This works, thanx.

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

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