Forum Discussion
Sorting Blank Values from Fact Table
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:
- I've added a column in PowerQuery with the sorting order:
- 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!
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.
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:
- I've added a column in PowerQuery with the sorting order:
- 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!
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.