Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
casperholtfarve
Helper III
Helper III

How to blank second lowest value in row & column if 1 cell is already blank

Hi all

We have a matrix that consists up of different columns from different tables:

casperholtfarve_0-1673259129190.png

In this table we the numbers created based on a measure that sums up the numbers.

This measure has included that, if the number is less than 5, then it will BLANK() the value.

 

The issue here is, that you are still able to calculate the number sometimes. 

E.g. look at '08 Installation....' here it is only year 2018 that is blank. And the the total i 45. So for this case we can easily find out what the value should be by adding the other years and comparing to the total. This is NOT what we want. We want to discrete these numbers and STILL show the total.

 

So what I would like to accomplish now is to build it in a way that:

  • If 1 and ONLY 1 cell is blank in the entire row, then also blank the second lowest value in the row. In this cases the number 7 in 2021 column.
  • If 1 and ONLY 1 cell is blank in the entire column, then also blank the second lowest value in the column.

 

Thanks in advance. It would help a LOT!

 

/Casper.   

1 ACCEPTED SOLUTION

Hi, @casperholtfarve 

Thank you for your quick response and patience in describing your problem.
For your problem, if you can mark me with some solution, it will be a great help and encouragement for me!
And I regret that in my current knowledge and ability, I can't help you completely solve your needs! Sorry very much. If you find the solution to this iteration in your future work, I hope you can share it with me!Let's examine Power BI together!

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

34 REPLIES 34
Wtazz82
Regular Visitor

Hello sir!  Can you please help me with this. Please🙏🙏

Whenever I try to connect folder (from "Get Data" option),the browse option pop ups and then if I browse, I only get the "Desktop" option. How can I connect the folder option with my file manager so that after clicking the "browse" option file manager's drive,folder etc all show up!!! Please help me

CamScanner 01-30-2023 14.50_1.jpg

v-yueyunzh-msft
Community Support
Community Support

Hi , @casperholtfarve

Thanks for your quick response and sorry for the delay response due to the different work time zone.

I do not understand the "Same logic different pane/table. for 2018 column there is only 1 blank cell. It should be 2?"?
Can you explain it more detailed?

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Hi @v-yueyunzh-msft 
Thanks for responding.

Well I use the same code but for different fields in another power BI report.
Here I see, that the matrix is working fine EXCEPT the first cell in the matrix. ANd the first column.

If you see this screenshot:

casperholtfarve_0-1673508719163.png

In the column 2018 and the first row. 
This is the second lowest value of the column. So I expect that this should be blank as well, since we do only have 1 blank value in that row.

Does it make sense, or should I create a PBIX report?

 


 

Hi , @casperholtfarve 

Thanks for your quick response!

In the picture you provide , it seems the blank value in first column may be blanked by the yellow?

vyueyunzhmsft_0-1673512161557.png

I guess this blank value may be produced by only one value in the last row.

If so, then this one is in line with expectations.

In my method, there is no way to implement loop iterative deletion. Unique sequential deletions can only be made based on the original table.

If you're going to iterate over deletion until your criteria aren't met, there's probably no way to do it in Power BI with my ability and knowledge.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

 

Hi @v-yueyunzh-msft 

I think you misunderstand.
I expect another value in the first column to be blank.
The top one (yellow). The row is just fine. No problem at all.

But we need to blank two values in BOTH rows and columns. And the 2018 colum does not have 2 blank values but only 1.

casperholtfarve_0-1673512531329.png

 



Hi , @casperholtfarve 

Yes, now in the first column , it has only one blank value . In Logic , it needs to blank the 2nd value in 1st column.

I mean if the "red 1 you flag" is blank by other blank value , it will not work.
If not , can you provide the sample .pbix file to me to test in my side?

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Hi @v-yueyunzh-msft 
Yes, the red 1 I flag is being blank due to another value. So that means, that the loop iteration part is not possible, probably to make it fully dynamic in that way using Power BI alone?

/Casper.

Hi, @casperholtfarve 

That's right, and so far I haven't found a way to implement loop iteration in Power BI to remove a visual value.
Because I didn't find a DAX function about loops.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi @v-yueyunzh-msft 
Okay. I understand.

THANK YOU SOOOOO MUCH for your help though.
I got a lot more knowledge and got something that still improved what I had today.

I really appricate the help you offered. 
I am not sure that I can accept the entire solution, since it does not FULLY comprehent what I need. But I hope that is okay for you, still? 

Thanks again.

/Casper.

Hi, @casperholtfarve 

Thank you for your quick response and patience in describing your problem.
For your problem, if you can mark me with some solution, it will be a great help and encouragement for me!
And I regret that in my current knowledge and ability, I can't help you completely solve your needs! Sorry very much. If you find the solution to this iteration in your future work, I hope you can share it with me!Let's examine Power BI together!

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Ahmedx
Super User
Super User

Do you need it?

Screen Capture #214.png

Hi @Ahmedx 
What do you mean, if I need it?


@v-yueyunzh-msft it seems not to be entirely bullet proof:

casperholtfarve_0-1673443324096.png


Same logic different pane/table. for 2018 column there is only 1 blank cell. It should be 2?
This is the measure:


Antal ulykker disk. bfa= 

VAR _row =
CALCULATETABLE(
    VALUES(Produktionsenhed[P-enhed - Branchefællesskab]),
    ALLSELECTED(Produktionsenhed[P-enhed - Branchefællesskab])
)

VAR _column =
CALCULATETABLE(
    VALUES('Tid registreringsdato'[Registrering - År]),
    ALLSELECTED('Tid registreringsdato'[Registrering - År])
)

VAR _table =
ADDCOLUMNS(
    CROSSJOIN(
        _row,
        _column
    ),
    "Diskretionering",
    [Antal ulykker]
)

VAR _bfa = 
CALCULATE(
    MAX(Produktionsenhed[P-enhed - Branchefællesskab]),
    ALL('Tid registreringsdato'[Registrering - År])
)

VAR _year = 
CALCULATE(
    MAX('Tid registreringsdato'[Registrering - År]),
    ALL(Produktionsenhed[P-enhed - Branchefællesskab])
)

VAR _row_blank_table =
FILTER(
    _table,
    Produktionsenhed[P-enhed - Branchefællesskab] = _bfa && [Diskretionering] = BLANK()
)

VAR _row_blank_count = COUNTROWS(_row_blank_table)

VAR _second_lowest_value_row =
MINX(
    FILTER(
        _table,
        Produktionsenhed[P-enhed - Branchefællesskab] = _bfa && [Diskretionering] <> BLANK()
    ),
    [Diskretionering]
)

VAR _second_lowest_year_row =
MINX(
    FILTER(
        _table,
        Produktionsenhed[P-enhed - Branchefællesskab] = _bfa && [Diskretionering] = _second_lowest_value_row
    ),
    'Tid registreringsdato'[Registrering - År]
)

VAR _column_blank_table =
FILTER(
    _table,
    'Tid registreringsdato'[Registrering - År] = _year && [Diskretionering] = BLANK()
)

VAR _column_blank_count = COUNTROWS(_column_blank_table)

VAR _second_lowest_value_column = 
MINX(
    FILTER(
        _table,
        'Tid registreringsdato'[Registrering - År] = _year && [Diskretionering] <> BLANK()
    ),
    [Diskretionering]
)

VAR _second_lowest_type_column = 
MINX(
    FILTER(
        _table,
        'Tid registreringsdato'[Registrering - År] = _year && [Diskretionering] = _second_lowest_value_column
    ),
    Produktionsenhed[P-enhed - Branchefællesskab]
)


RETURN
SWITCH(
    TRUE(),
    _row_blank_count = 1 && [Antal ulykker] = _second_lowest_value_row && _year = _second_lowest_year_row,
    BLANK(),
    _column_blank_count = 1 && [Antal ulykker] = _second_lowest_value_column && _bfa = _second_lowest_type_column,
    BLANK(),
    [Antal ulykker]
)

@v-yueyunzh-msft It's the first error i find and I haven been testing a lot.
Might it be because it is the absolute first cell? Like first row AND first column?

Thanks in adance. 
Hope you can be just as helpful as you have already been 🙂

 

/Casper.

v-yueyunzh-msft
Community Support
Community Support

Hi , @casperholtfarve 

According to your description, you want to delete the second minest value if the row and column only have one blank value.

As belows, you need to delete the red color value , like this:

vyueyunzhmsft_0-1673416396679.png

My test value measure is this:

Value Measure = IF( SUM('Table'[Value])>=5,  SUM('Table'[Value]), BLANK())

Here are the steps you can refer to :
(1)This is my test data:

vyueyunzhmsft_1-1673416436014.png

(2)Then we can create a measure like this:

Measure = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('Table'[TYPE]) , ALLSELECTED('Table'[Year])) , "Value Test",[Value Measure])
var _type = MAX('Table'[TYPE])
var _year = MAX('Table'[Year])
var _row_blank_table = FILTER(_t , [TYPE] = _type&& [Value Test]=BLANK())
var _row_blank_count = COUNTROWS(_row_blank_table)
var _second_minest_value_row  = MINX(FILTER(_t , [TYPE] = _type&& [Value Test]<>BLANK()) , [Value Test])
var _column_blank_table = FILTER(_t , [Year] = _year&& [Value Test]=BLANK())
var _column_blank_count =COUNTROWS(_column_blank_table)
var _second_minest_value_column  = MINX(FILTER(_t , [Year] = _year&& [Value Test]<>BLANK()) , [Value Test])

return
SWITCH(TRUE(),
_row_blank_count=1 &&[Value Measure]= _second_minest_value_row , BLANK(),
_column_blank_count=1&&[Value Measure]=_second_minest_value_column,BLANK() , 
[Value Measure])

 (3)Then we can get the result:

vyueyunzhmsft_2-1673416500566.png

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thanks, I will take a look at your solution at get back to you 🙂
Is the solution dynamic also?
Meaning, that if the second lowest value in a row is in a column with no blank values, this means that the column now has 1 blank value and should find another lowest value to blank out?

 

/Casper.

Hi , @casperholtfarve 

Oh .. Do you mean if in a row , it has only one value then i blank the second minest value . Then the "second minest value " for the column is a new blank value , it will not be delete in this column.

If you want to achieve a second minimum value of deleting a row and then determine whether the column meets the condition, I think it is difficult for Power BI to implement your needs. My method is to only delete once on the basis of your existing one, and will not iteratively delete.

Iterative deletion is very difficult to implement in Power BI Desktop, and if you want to implement it, you still need to use other tools, such as Python.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Thanks.

Yea I just tried it out on my own data.
Initially it works perfectly. But once I start to use filters to narrow down results, it brakes and doesn't work.

 

Here I filter 3 different things to narrow down the results and here I would expect/want it to also hide some values in the column for 2020 and 2021, since there is only 1 value present.

But are you saying that this is not doable in Power BI?
I have 3 filters that can be selected. I dont know if it is possible to include these in the logic?
I am not deleting og adding rows to the data. Only filtering.

Cheers and thanks again
/Casper.

casperholtfarve_0-1673419857296.png

 

Hi , @casperholtfarve 

Thanks for your quick response!

According to your description, my method does not work when you are using the slicer.

You need to check this, the function is using the ALLSELECTED() function.

vyueyunzhmsft_0-1673420356678.png

And in my side , we can use the slicer like this:

vyueyunzhmsft_1-1673420613647.png

vyueyunzhmsft_2-1673421266375.png

 

Also, based on your screenshots, I can't see the problem if i have no your test data, you can try to describe your problem more clearly, if convenient, you can provide your .pbix file (does not contain sensitive data) and describe the problem in detail?

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi Aniya

In my screenshot you se that 1 row has 2 blank fields. This is fine.

But now there is only 1 blank field in both columns (2021 and 2022).

 

If i look at column 2020 for example I can now just add all these numbers together and subtract the column total. So now I know what should be in the missing field. That is the entire purpose of ALWAYS blanking out at least 2 fields in BOTH the columns and the rows.

The filters I use at not realted to either the column og row attributes. 
They are from different tables.

I am not sure that I can share the pbix-file since this is kind of sensitive to my work.

 

Hope it makes sense, otherwise just reply back again.

 

/Casper.

Hi , @casperholtfarve 

That's right, I'm seeing in your [2020] and [2021] columns that there is currently and only one blank value, but the second smallest value is not removed at the moment, which is strange. Because in my method, I dynamically determine the row and column headers in the current filtering context, and I use the ALLSELECTED() function, normal logic can use slicers to filter.

I'll explain my logic, you can check it yourself:
(1) _t: Indicates that a virtual table like the original visual is built in the current filtering context (indicating the TYPE and Year that exist after being filtered by the slicer).
(2) _type, _year: Get the corresponding row and column values placed in the Matrix visual visual.
(3) _row_blank_count: Find the number of several BLANK values existing in the current row.
(3) _second_minest_value_row: Find the second smallest value of the current row.
(4) _column_blank_count, _second_minest_value_column : the same as above.
(5) You can try to put the above variable in the return value of return for debugging, and see which step has the problem?

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.