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.

Preboat's avatar
Preboat
Frequent Visitor
1 year ago

Hi v-veshwara-msft 

I have used a sample data since the original is connected to the server. Essentially, what i want to achieve is to filter the matrix visual to just show rows where the rate is greater than 0.5.
FILTERING PROBLEM2.pbix
Thanks

v-veshwara-msft's avatar
v-veshwara-msft
Community Support
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.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi Preboat ,
    Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
    Thank you.