Forum Discussion

selwynvr's avatar
selwynvr
Frequent Visitor
2 years ago
Solved

ALLNOBLANKROW

Hi all
I am using ALLNOBLANKROW because it removes the blanks nicely fom the related table.
I however would like the Slicer filter at the top of my report (UW month) to also limit the results.
Is there a better way of doing this?
Code below:
CALCULATE(
MAX('DimUWMonth'[Month]),
ALLNOBLANKROW('FactPaymentPattern')
)

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi selwynvr ,

     

    You can try the following steps.
    1. Create a calculated table.

    Table 2 = 
DISTINCT('Table'[UW Month])


    2. create MEASURE.

    MEASURE = 
VAR _num1 =
    VALUE ( LEFT ( MAX ( 'Table'[UW Month] ), 2 ) )
VAR _num2 =
    VALUE ( LEFT ( MAX ( 'Table 2'[UW Month] ), 2 ) )
RETURN
    IF ( _num1 <= _num2, 1 )


    3. Filter the data with a MEASURE value of 1.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    selwynvr Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • selwynvr's avatar
      selwynvr
      Frequent Visitor
      2 years ago

      Hi Greg_Deckler
      Thank you for responding
       
      Current Output (for New Measure column)

      Uw MonthNew MeasureFactPaymentPattern Division
      16/1724/251
      17/1824/251
      18/1924/251
      19/2024/251
      20/2124/251
      21/2224/251
      22/2324/251
      23/2424/251
      24/2524/251


      Desired output when the Slicer at the top of the report says the Max is now 23/24

      UW MonthNew MeasureFactPaymentPattern Division
      16/1723/241
      17/1823/241
      18/1923/241
      19/2023/241
      20/2123/241
      21/2223/241
      22/2323/241
      23/2423/241


      Please let me know 
      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi selwynvr ,

     

    You can try the following steps.
    1. Create a calculated table.

    Table 2 = 
DISTINCT('Table'[UW Month])


    2. create MEASURE.

    MEASURE = 
VAR _num1 =
    VALUE ( LEFT ( MAX ( 'Table'[UW Month] ), 2 ) )
VAR _num2 =
    VALUE ( LEFT ( MAX ( 'Table 2'[UW Month] ), 2 ) )
RETURN
    IF ( _num1 <= _num2, 1 )


    3. Filter the data with a MEASURE value of 1.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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