Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Issus combining USERELATIONSHIP and FILTER in a measure

Hi, I am very new to Power BI and I can’t seem to resolve my most recent issue.  I have searched for a solution to this issue without any success.  If someone out there can help me, I would really a...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    You can try this:

    If it doesn't work correctly,maybe it’s a behavior of ‘USERELATIONSHIP’,The filter added later still uses the original relationship.If you don't use filter, the 'calculate' function will automatically filter the corresponding data in inactive relationships(date-resolved) based on the current context.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

  • Anonymous's avatar
    Anonymous
    5 years ago

    After additional research and debugging, I was able to come up with the following measure that does exactly what I need.  Below is what I changed:

    1. Removed the "('Issues'[Resolved] <> BLANK()) line since it wasn't adding any value to my results.  
    2. Added "ALL" to my FILTER line
    3. Added VARs to enable the readability,  enable the year and month compare in my IF statement to display values in my line graph up to the current year/month.

     

    VAR FirstShippedDate = CALCULATE(MINX('Issues', 'Issues'[Resolved] ) , ('Issues'[State] = "Shipped" ), ALL ('Issues' ))
    VAR LastShippedDate = CALCULATE (MAXX('Issues', 'Issues'[Resolved - Copy-yyyy-mm]) ,('Issues'[State] = "Shipped" ), ALL ('Issues' ) )
    Var MaxCalendarDate = MAXX('Date', 'Date'[Year-Month])

    Return
    IF ( MaxCalendarDate > LastShippedDate && MaxCalendarDate > TODAY(),
    BLANK(),
    CALCULATE( COUNT ( 'Issues'[Resolved] ),
    ('Issues'[State] = "Shipped" ),
    USERELATIONSHIP ('Date'[Date], 'Issues'[Resolved] ) ,
    FILTER ( ALL('Date' ), ('Date'[Date] <= MAX('Date'[Date]) ))
    ) )

     

    Thanks so much for implying that there were issues with my measure and guiding me to dig deeper.  I initially thought that a FILTER statement could not follow a USERELATIONSHIP statement, but that is not the case.  

     

    Thanks!!!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you so much for your response.  FYI:  The measure works great without the FILTER statement.  The issue I am incountering is that the FILTER statement I have added to the end of the measure is not providing me a running total in the "Shipped Total" column as expected.  Dy you have any advise about how to incoorporate this FILTER statement so that I can get the running total?  Below is the measure I am using with the filter included.  

 

Shipped Monthly = (
CALCULATE (COUNT ('Issues'[Resolved]),
      ('Issues'[State] = "Shipped" ) ,
      ('Issues'[Resolved] <> BLANK() ) ,
      USERELATIONSHIP ('Date'[Date], 'Issues'[Resolved] ) ,
      FILTER(('Date'), 'Date'[Date] <= MAX ('Date'[Date]) )

 

Running this measure creates the following data in the "Shipped Total' column".  It appears to be ingnoring the FILTER completly.  

Month

Upload Total

Shipped Total

January

1

 

February

4

 

March

8

2

April

9

 

May

16

5

June

24

6

July

 

3

August

32

 

September

48

7

October

56

6

November

70

6

December

 

 

The desired results should look as follows in the "Shipped Total' column":

Month

Upload Total

Shipped Total

January

1

 

February

4

 

March

8

2

April

9

2

May

16

7

June

24

13

July

 

16

August

32

16

September

48

23

October

56

29

November

70

35

December

 

 

 

Does anyone have any advice on how I can get the running total to appear in the 'Shipping Total" column?  

Any advice provided would be most appreciated.  I am completly out of ideas!!!

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

 

You can try this:

If it doesn't work correctly,maybe it’s a behavior of ‘USERELATIONSHIP’,The filter added later still uses the original relationship.If you don't use filter, the 'calculate' function will automatically filter the corresponding data in inactive relationships(date-resolved) based on the current context.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    After additional research and debugging, I was able to come up with the following measure that does exactly what I need.  Below is what I changed:

    1. Removed the "('Issues'[Resolved] <> BLANK()) line since it wasn't adding any value to my results.  
    2. Added "ALL" to my FILTER line
    3. Added VARs to enable the readability,  enable the year and month compare in my IF statement to display values in my line graph up to the current year/month.

     

    VAR FirstShippedDate = CALCULATE(MINX('Issues', 'Issues'[Resolved] ) , ('Issues'[State] = "Shipped" ), ALL ('Issues' ))
    VAR LastShippedDate = CALCULATE (MAXX('Issues', 'Issues'[Resolved - Copy-yyyy-mm]) ,('Issues'[State] = "Shipped" ), ALL ('Issues' ) )
    Var MaxCalendarDate = MAXX('Date', 'Date'[Year-Month])

    Return
    IF ( MaxCalendarDate > LastShippedDate && MaxCalendarDate > TODAY(),
    BLANK(),
    CALCULATE( COUNT ( 'Issues'[Resolved] ),
    ('Issues'[State] = "Shipped" ),
    USERELATIONSHIP ('Date'[Date], 'Issues'[Resolved] ) ,
    FILTER ( ALL('Date' ), ('Date'[Date] <= MAX('Date'[Date]) ))
    ) )

     

    Thanks so much for implying that there were issues with my measure and guiding me to dig deeper.  I initially thought that a FILTER statement could not follow a USERELATIONSHIP statement, but that is not the case.  

     

    Thanks!!!

    • rusgesig's avatar
      rusgesig
      Icon for Helper IV rankHelper IV
      1 year ago

      Useful post, my issue was that measure would not filter if <>blank() was before USERELATIONSHIP, removing the '[col] <> blank()' line fixed the filtering.