FILTERING PROBLEM

[Preboat]

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[CODE] = "2")

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

this works fine filtering at the total level but not at the detail level for example
the code give me the  result below. filtering where amount is > 0.5 at the total level and row figures remain like in case of name ewrr  and mmomm the totals are filtered but not the entire row.

NAME	57	6	20	11	30	40	Total
wdw dnwu	490		34				524
ewrr	30	546	3		4
jkmdm	25					30	55
mmomm	4		2			40
Total	549	546	39	0	4	70	579

[v-veshwara-msft]

Hi @Preboat ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @Preboat ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @Preboat ,
Thanks for reaching out to Microsoft Fabric Community.
To achieve the expected behavior where rows are hidden if they don't meet the condition and totals are also blanked accordingly, I used the following two DAX expressions:

Measure for the matrix value:

TOTAL Amt Row Filter = 
VAR _TDA = CALCULATE(SUM(DE[SUMAMT]), DE[STATUS] = "Deed", ALLEXCEPT(DE, DE[NAME]))
VAR _TB = CALCULATE(SUM(DE[SUMbill]), ALLEXCEPT(DE, DE[NAME]))
VAR _RATE = DIVIDE(_TDA, _TB, 0)
VAR _Result = IF(ISBLANK(_TDA) || _RATE > 0.5, BLANK(), SUM(DE[SUMAMT]))
RETURN
_Result

Supporting measure to filter rows in the visual:

ShowRow = 
VAR _TDA = CALCULATE(SUM(DE[SUMAMT]), DE[STATUS] = "Deed", ALLEXCEPT(DE, DE[NAME]))
VAR _TB = CALCULATE(SUM(DE[SUMbill]), ALLEXCEPT(DE, DE[NAME]))
VAR _RATE = DIVIDE(_TDA, _TB, 0)
RETURN
IF(_RATE <= 0.5, 1, 0)

Then, in the matrix visual, added ShowRow to the filter pane and set it to show only when the value is 1.

With this setup, only rows where the rate condition is met are displayed, and the total is correctly calculated from those rows.

Sample data:

Output:

Hope this helps. Please reach out for further assistance.

Attached .pbix for reference.

Thnak you.

[Preboat]

Hi @v-veshwara-msft 

Thanks for your suggestion. This is how my table looks like currently 👇

Name	24	7	30	4	15	Grandtotal	Total Bill	Rate
c n ii	30	2	3	4	4	43	754	5.70%
wewdd	4	1	40	1	1	47	75	62.67%
wee	4	10	30	5	40	89	170	52.35%
nvnn	3	2	43	3	32	83	350	23.71%
Grandtotal	41	15	116	13	77	262	1349	144%

And my code as indicated is giving me blanks at just the total column without removing the individual rows also where the rate is >0.5. I tried your suggestion but it is still not removing those rows. Below is what my result is looking like currently.

Rows "c n ii"  and "nvnn" where the rate is less than 0.5 has the grandtotal blanks but the detail row remains but i want that whole row taken out.

Name	24	7	30	4	15	Grandtotal	Total Billed	Rate
c n ii	30	2	3	4	4		754	5.70%
wewdd	4	1	40	1	1	47	75	62.67%
wee	4	10	30	5	40	89	170	52.35%
nvnn	3	2	43	3	32		350	23.71%
Grandtotal	41	15	116	13	77	262	1349	144%

Thanks

[v-veshwara-msft]

Hi @Preboat ,

Thanks for the update.

Since the issue still persists even after trying the suggested approach, could you please share the .pbix file so we can assist more accurately and effectively.

You can upload the file to your OneDrive or Google Drive and share the link here with the necessary access permissions.

Please follow this guide to share sample data or .pbix: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[Preboat]

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 · Accepted solution]

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]

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

[danextian]

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]

@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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Greg_Deckler]

@Preboat What results are you expecting?

[Preboat]

Hi @Greg_Deckler  i was expecting the whole row to be taken off. In my example table i am expecting the whole row in red to be taken off and not just the total. Currently the total are filtering correctly to >0.5 condition and returnig blank where the >0.5 condition is met. But the figures that makes up the total remain (the row)

NAME	57	6	20	11	30	40	Total
wdw dnwu	490		34				524
ewrr	30	546	3		4
jkmdm	25					30	55
mmomm	4		2			40
Total	549	546	39	0	4	70	579