topic Re: DAX command to show top 4/bottom 4 variances in a matrix table in DAX Commands and Tips

DAX command to show top 4/bottom 4 variances in a matrix table

Anonymous — Wed, 08 Nov 2023 05:08:35 GMT

Hi,

I'm pretty new to Power BI so I might be missing an easier way of doing this. I'm trying to show the top 4 and bottom four variances from estimated value to actual value. The only way I've been able to display this is using a matrix table and using two measures, for rank and for variance.

I'm trying to limit the number of entries shown to four each but have had no success. I found a youtube video that shows how to do it but I'm getting a 'A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.' when using it with my variables used.

From what I've read I need to use a FILTER function to fix this, but I'm not sure where. Any advice/ideas how to achieve what I'm after?

Thanks!

Re: DAX command to show top 4/bottom 4 variances in a matrix table

ERD — Wed, 08 Nov 2023 10:18:48 GMT

Hi Anonymous , you have syntax issues and PBI is showing you where.

VAR _Losers = FILTER(..)

Re: DAX command to show top 4/bottom 4 variances in a matrix table

scee07 — Wed, 08 Nov 2023 10:24:29 GMT

Hi,

as far as I understood, there should be some measure that results in a a switch to show either the bottom records or the top records. To my knowledge one can only filter by table columns in Power BI (anybody correct me, if I am wrong). Anyway let's try a different approach.

I do some simualtion to get some toy data:

TransactionIDs = GENERATESERIES ( 1, 50, 1 ) TransactionIDs = GENERATESERIES ( 1, 50, 1 ) Transactions = CROSSJOIN ( EntityIDs, TransactionIDs ) Variation = SUMMARIZE ( Transactions, Transactions[EntityID], "Variation", [VariationMeasure] ) VariationMeasure = STDEV.P ( Transactions[Value] )

This is giving me values for some entities where I want to know bottom and top.

A calcuated column now qualifies a record as bottom or top or neither of the two.

TopOrBottom = VAR entityID = Variation[EntityID] VAR tops = FILTER ( TOPN ( 4, ALL ( Variation ), Variation[Variation], DESC ), [EntityID] = entityId ) VAR bottoms = FILTER ( TOPN ( 4, ALL ( Variation ), Variation[Variation] * -1, DESC ), [EntityID] = entityId ) VAR istop = COUNTROWS ( tops ) == 1 VAR isbottom = COUNTROWS ( bottoms ) == 1 RETURN IF ( isbottom, "IsBottom", IF ( istop, "IsTop", BLANK () ) )

The TopN DAX command just gives you the top n records according to one of the table's columns

The table looks like

Now, a filter can be applied to only show the tops or bottoms (if yo don't like the blank you can do a helper filter table without the blank and filter the above table with reference to the TopOrBottom field.

Hope this helps.

Best regards

Christian

Re: DAX command to show top 4/bottom 4 variances in a matrix table

Dangar332 — Wed, 08 Nov 2023 11:07:46 GMT

Hi, Anonymous

you have placehholder error
use your measure inside of filter ()
refer below solved LINK

Re: DAX command to show top 4/bottom 4 variances in a matrix table

Anonymous — Wed, 08 Nov 2023 22:22:22 GMT

Hi Christian,

Thanks for responding, unforutnately I need to show top + bottom at the same time in the same table, and there's also no room for slicers/checkboxes in my dashboard (it's also going to be printed so can't use the functionality).

Re: DAX command to show top 4/bottom 4 variances in a matrix table

scee07 — Thu, 09 Nov 2023 07:56:40 GMT

Hi,

I am not sure I am following you. All the values are already in the table with the calculated column. Never mind. If the correction of your formular below by ERD does not solve your problem, you might send an excel screenshot of what you final result should be.

Best regards

Christian