Sorting Blank Values from Fact Table

[stefanjt]

Dear Community,

I need your help with something that may seem simple but I've been struggling with for a while, even after doing some research, trial and error.

I have a dimension table connected to a fact table. Not all entries from the dimension table are present in the fact table. I'm showing a table visual with a few columns coming from the dimension table and a whole number column coming from the fact table. For those entries from the dimension table that are missing from the fact table, the whole number column from the fact table comes out as blank. How do I ensure that sorting by the whole number column will always show the blank values in the end, regardless of the sorting order (ascending or descending)? I need to show the rows from the dimension table that have no corresponding entries in the fact table.

Thank you in advance!

[Anonymous]

Hi @stefanjt 

Thank you very much Sahir_Maharaj for your prompt reply.

May I ask if your problem has been solved? If not, let me share something with you.

Due to the default design of power bi desktop, it sorts all data in visual as a whole. So your desire to keep null values out of the sort may be difficult to achieve. But I can provide you some other ways.

Create a measure.

SortOrder = 
var _blank = IF(
    NOT(ISBLANK(SUM('FactScore'[Score]))),
    1, 
    BLANK()
)
var _rank = 
RANKX(
    ALL('FactScore'),
    CALCULATE(SUM('FactScore'[Score])),
    ,
    ASC,
    Dense
)
RETURN
IF(
    _blank = 1,
    _rank,
    BLANK()
)

You can get:

This way, you can keep null values from being inserted into non-null values by ascending or descending the [SortOrder] column.

This approach accomplishes as much as possible for your purposes.

Regards,

Nono Chen

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

[stefanjt · Accepted solution]

Hi, @Anonymous and thanks for the suggestion. It has pointed me toward the solution that works for me.

The original requirement was to display dimension table entries without corresponding scores in the fact table, in the bottom of the table visual regardless of the sorting order, instead of in the middle as it was before. I've achieved showing them at the end when sorting DESC, in the following way:

1. I've added a column in PowerQuery with the sorting order:
2. I've sorted the Score group by the new column:

The result is this: 

I guess this is the best that can be done at the moment. Report: https://www.dropbox.com/scl/fi/tuaslajaw737hccyhtwzk/Sorting-Blanks-Demo-Report.pbix?rlkey=cfjdy6566bh6w69g1oybn1kgh&dl=0 

Thanks all for your help!

[Anonymous]

Hi @stefanjt 

Thank you very much for sharing! You can accept it as a solution, which helps to help others discover the problem!

Regards,

Nono Chen

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

[stefanjt]

Hi, @Sahir_Maharaj and thanks for taking the time to provide some suggestions.

Unfortunately, I'm unable to implement them as I do not have a sort column from the dimension table. I'm referring to the standard sorting capability when you click on a heading of a table visual.

I am attaching a sample report - https://www.dropbox.com/scl/fi/rce0wv2bndqo96pddsrsq/Sorting-Blanks-Demo-Report.pbix?rlkey=etzblw6ze....

The requirement is to show the rows from the dimension table that do not have matches from the fact table but always at the bottom, regardless of the sorting order. At the moment they show in the middle:

[Sahir_Maharaj]

Hello @stefanjt,

Can you please try this approach:

Sorted Value = 
IF(
    ISBLANK('FactTable'[WholeNumberColumn]),
    IF(SELECTEDVALUE('DimensionTable'[Sort Order]) = "Ascending", 9999999, -9999999),
    'FactTable'[WholeNumberColumn]
)