Matrix BUG in PBI ?

[Rabelo]

😅Hy Guys !! What a Challange !!!

This is a simplified  example of a more complex project. Dont worry if it may seems nonsense ... the idea was to reply the fault!

An error happens always when only one period has data. If two ore mores periods have data, no error occurs!

I tried to upload .pbix, but seems impossible. Hasonvalue()  function was used instead of Total (Sales / Sales Acc) . Rankx function creates correctly a ranking inside the table ... but the hasonvalue( ) function seems to crash when there is only one period with data , placing values instead of Ranking (modified by the hasonvalue()  function inside measure). 😅 Thks All !!!

 Thks All !!!!!!!!!!!!!

[Rabelo]

Hi Carson !
Thks so much for you complete answer, bringing concepts etc ...
Your answer not only resolved the my problem, such as let me learn more about the particularities os theese 3 functions!

Best Regards to all suporters!!

[Anonymous · Accepted solution]

Hello,danextian and Ritaf1983 .thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hi,@Rabelo.I am glad to help you.

I think it has to do with the calculation logic of the function itself, you can try using the ISINSCOPE function.

In Power BI matrix, we often use ISINSCOPE, ISFILTERED and HASONEVALUE to check the status of filters in the data model.
Generally used to determine hierarchical structure, there is a difference between the three of them:

ISINSCOPE
Purpose: Checks whether a particular column is in the current context.
Example: ISINSCOPE('Table'[Column]) Return TRUE: If 'Table'[Column] is in current context.
Typically used in hierarchies to determine if a level is in the current context. (This function is the one I think is most commonly used in matrices to determine if the current row is filtered by a field, and thus to control the value of the Total column)
ISFILTERED
Purpose: To check whether a particular column is directly filtered.
Example: ISFILTERED ('Table' [Column]) return TRUE: If 'Table' [Column] is directly filtered.
Generally used to determine whether a column is directly filtered by a filter or slicer (usually used with slicer)
HASONEVALUE
Purpose: Checks if a particular column has only one unique value.
Example: HASONEVALUE('Table'[Column]) Return TRUE: If 'Table'[Column] has only one unique value.
This is my test:

ISFILTERED and ISINSCOPE:
These two functions behave more consistently when dealing with Total rows because they both check if the column is filtered or in the current context.
ISFILTERED checks to see if the column is filtered directly, while ISINSCOPE checks to see if the column is in the current context in the Total row, and both functions typically return FALSE, so the total row uses the value of _totalRankx.

HASONEVALUE checks to see if the column has only one unique value in the Total row, which would normally have more than one value, so HASONEVALUE returns FALSE, resulting in the value of _totalRankx being used. But for the Seller = “Jeff” row, there is only one value out of 876 in the current calculation environment, so in this row HASONEVALUE returns Ture, which incorrectly outputs the value of _sumSales.

This is my test data and code:

The measure :

SalesEachSellerSUM = 
VAR _period =
    MAX ( 'TestSales'[Period] )
VAR _seller =
    MAX ( 'TestSales'[Seller] )
VAR _sumSales =
    CALCULATE (
        SUM ( 'TestSales'[Sales] ),
        FILTER ( ALL ( 'TestSales' ), 'TestSales'[Seller] = _seller )
    )
RETURN
    _sumSales

M_TotalRankx = 
RANKX ( ALL ( 'TestSales' ), [SalesEachSellerSUM],, DESC, DENSE )

Sales AccISINSCOPE = 
VAR _period = MAX ( 'TestSales'[Period] )
VAR _seller = MAX ( 'TestSales'[Seller] )
VAR _sumSales =
    CALCULATE (
        SUM ( 'TestSales'[Sales] ),
        FILTER (
            ALL ( 'TestSales' ),
            'TestSales'[Seller] = _seller
                && 'TestSales'[Period] <= _period
        )
    )
VAR _totalRankx = [M_TotalRankx]
RETURN
   
    IF ( ISINSCOPE( TestSales[Period] ), _sumSales, _totalRankx )
// Or use  IF ( ISFILTERED( TestSales[Period] ), _sumSales, _totalRankx )

Sales AccHASONE = 
VAR _period = MAX ( 'TestSales'[Period] )
VAR _seller = MAX ( 'TestSales'[Seller] )
VAR _sumSales =
    CALCULATE (
        SUM ( 'TestSales'[Sales] ),
        FILTER (
            ALL ( 'TestSales' ),
            'TestSales'[Seller] = _seller
                && 'TestSales'[Period] <= _period
        )
    )
VAR _totalRankx = [M_TotalRankx]
RETURN
    IF ( HASONEVALUE  ( TestSales[Period] ), _sumSales, _totalRankx )
//Measure for outputting error results

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @Rabelo 

If you're using HASONEVALUE then it is not a bug but an expected result as at the total column for Seller = Jeff, there is only one period value which is 1. Try using HASONEFILTER which will return true only at each period or if there is a filter applied directly to the period column.

[Ritaf1983]

Hi  @Rabelo 

Unfortunately, it is not possible to understand the data model and what you are trying to achieve based on the description and image. You can save the PBIX file in any public cloud and share it via a link.
Additionally, it is recommended to also show the desired result.