Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Card showing an unknown value

Hi Bi Community.

I have a table that has two columns:
Column 1 = week end date
Column 2 = value

Both columns 1 and 2 are pulled from an excel spreadsheet dynamically.

I'm using the measure that I've posted below but when I display this in a card it gives me a value of 5423. I have absolutely no idea where this value is coming from.

I've attached pics of the table and the last few values from the excel spreadsheet where 'Weekly average Packs Per Hr' [Weekly hourly average] pulls the data . 

The desired end result is: find last none blank value from 'Weekly average Packs Per Hr' [weekly hourly average], show this & update dynamically as the excel spreadsheet updates.

 

29/05/20223829
05/06/20223953
12/06/20223857
19/06/20223834
26/06/20223781
03/07/20223955
10/07/20222737
17/07/20223507
24/07/20223681
31/07/20223534
07/08/20223749
14/08/20223760
21/08/20223257
28/08/20223783
04/09/20223961
11/09/20223971
18/09/20223855
25/09/20223913
02/10/20224055
09/10/20224169
16/10/20224031
23/10/20223993
30/10/2022 
06/11/2022 
13/11/2022 
20/11/2022 
27/11/2022 
04/12/2022 
11/12/2022 

 

 

 

 

 

 

 

Need Help
  • mangaus1111's avatar
    mangaus1111
    3 years ago

    I think the problem is in ISBLANK function, that requires a column as argument

     

    NOT ISBLANK('Table'[Column])

6 Replies

  • mangaus1111's avatar
    mangaus1111
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Anonymous ,

    try this measure

    Measure =
    VAR _MaxDate = MAXX(
                       FILTER('Facts48',
                               NOT ISBLANK('Facts48'[Value])
                         ),
    'Facts48'[Date]
    )
    RETURN
    CALCULATE(MIN('Facts48'[Value]),'Facts48'[Date]=_MaxDate
    )
    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
    3 years ago
    last value (1) =
    VAR __A =
    MAX( 'Weekly average Packs Per Hr'[Week End Date] )
    VAR __B =
    CALCULATE(
    MAX('Weekly average Packs Per Hr'[Week End Date] ) ,
    FILTER(ALLSELECTED('Weekly average Packs Per Hr'), 'Weekly average Packs Per Hr'[Week End Date] < __A && 'Weekly average Packs Per Hr'[Weekly hourly average] <> BLANK() )
    )
    RETURN
    IF (
    ISBLANK ( MAX('Weekly average Packs Per Hr'[Weekly hourly average] )),
    CALCULATE( MAX( 'Weekly average Packs Per Hr'[Weekly hourly average]), FILTER(ALLSELECTED('Weekly average Packs Per Hr'), 'Weekly average Packs Per Hr'[Week End Date] = __B ) ) ,
    MAX('Weekly average Packs Per Hr'[Weekly hourly average] )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    mangaus1111, thank you for your response. I've tried it but now getting a syntax error and cannot figure out why. Any ideas? I'm new to power Bi. Sorry. 

     

     

     

    Measure = 
VAR _MaxDate = MAXX(
        FILTER('Weekly average Packs Per Hr',
            NOT ISBLANK('Weekly average Packs Per Hr')
        ),
'Weekly average Packs Per Hr'[Week End Date]
)
RETURN
CALCULATE(MIN('Weekly average Packs Per Hr'[Weekly hourly average]),'Weekly average Packs Per Hr'[Week End Date]=_MaxDate
)

     

    • mangaus1111's avatar
      mangaus1111
      Icon for Solution Sage rankSolution Sage
      3 years ago

      I think the problem is in ISBLANK function, that requires a column as argument

       

      NOT ISBLANK('Table'[Column])

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        mangaus1111 still no joy. same syntax error. 

        Measure = 
VAR _MaxDate = MAXX(
        FILTER('Weekly average Packs Per Hr',
            NOT ISBLANK('Weekly average Packs Per Hr'[Weekly hourly average])
        ),
'Weekly average Packs Per Hr'[Week End Date]
)
RETURN
CALCULATE(MIN('Weekly average Packs Per Hr'[Weekly hourly average]),'Weekly average Packs Per Hr'[Week End Date]=_MaxDate
)