Forum Discussion

Preboat's avatar
Preboat
Frequent Visitor
1 year ago
Solved

FILTERING PROBLEM

I have my dax code  TOTAL Amt = VAR _TDA = CALCULATE(SUM(DE[SUMAMT], DE[STATUS] ="Deed" VAR _TB = SUM(DE[SUMbill]) VAR _RATE = DIVIDE([TAD], [TAB]) VAR _ADJUTOTALS=[TDA] - CALCULATE['DE[TAD], DE...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi Preboat ,
    Thanks for sharing the PBIX.

    I checked the setup and noticed that the ShowRow logic you had earlier was being used separately, which meant the visual still showed unwanted rows unless filtered explicitly.

     

    I’ve modified the calculation items to embed the ShowRow flag logic directly inside them, so that they return BLANK() when the rate is less than or equal to 0.5.

    TDA = 
IF( [ShowRow_Flag] = 1, SELECTEDMEASURE(), BLANK() )
    TBA = 
IF(
    [ShowRow_Flag] = 1,
    IF(ISINSCOPE(Append1[Category]), BLANK(), SUM(Append1[SUMbill])),
    BLANK()
)
    Rate = 
IF(
    [ShowRow_Flag] = 1,
    IF(NOT ISINSCOPE(Append1[Category]), DIVIDE(SELECTEDMEASURE(), SUM(Append1[SUMbill]))),
    BLANK()
)

    This way, the matrix automatically hides those rows without needing an additional measure or filter pane configuration.

     

    Result:

     

    Attached the pbix file for reference.

    Hope this helps. Please reach out for further assistance.

    Thank you.

danextian's avatar
danextian
Super User
1 year ago

Hi Preboat

 

Your measure is being evaluated for each combination of your column and row headers and at the column total regardless of the row header. Create another measure that returns the total value for all the visible column headers and use that as a visual filter.

Name Total = 
CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Column Header] ) )

 

 

  • Preboat's avatar
    Preboat
    Frequent Visitor
    1 year ago

    danextian thank you for your response. The problem actually is my current code 
    TOTAL Amt =
    VAR _TDA = CALCULATE(SUM(DE[SUMAMT], DE[STATUS] ="Deed"
    VAR _TB = SUM(DE[SUMbill])
    VAR _RATE = DIVIDE([TAD], [TAB])
    VAR _ADJUTOTALS=[TDA] - CALCULATE['DE[TAD], DE[CODE] = "2")

    RETURN
    IF (_RATE >0.5, _TDA BLANK())

    filters the total with the >0.5 condition which I am ok with.  But I want to enhance the logic such that for the total that is returned as blank, i want the row to also remain blank. I do not want a blank total that is returning figures in the row.
    Thank you