Forum Discussion

dieterbe_vdb's avatar
dieterbe_vdb
Frequent Visitor
2 years ago

Different behavior Table and Matrix

I have some issues with a Matrix visualisation.

 

Data (source) table:


I want to visualize, using a matrix, the amount of customers based on both Status and Prio. Moreover, I want to calculate the total percentage of customers per Prio vs the total number of customer for a specific Status using a measure called % Customers.

Measures used:

 

# Qty = COUNT(Data[Customer ])
Total All Prio = CALCULATE([# Qty], REMOVEFILTERS(Data[Prio]))
% Customers = DIVIDE([# Qty], [Total All Prio])
 
Everything works fine untill I add a slicer filtering out a couple of customers.

When I filter on customer A and D for example, the Table still shows the results that I expect:

  • Total All prio measure = 3 for Status Active and Prio Low as there are 3 rows in total from customers A and B who are in the Active State and the Prio filter is removed thanks to the measure used
  • The Matrix is showing me non expected figures => why does the measure Total All prio results in a value of 2 for Status Active and Prio Low? I expect the measure Total All Prio to be the same for Active High and Active Low samples (which is the case using the Table visualization) but apparently the Customer slicer does influence the filter context in a Matrix differently than it does for a Table. 

 

 

Any idea what is going on here?

I've attached the pibx file I'm using.

Many thanks for your help!

 

Table:

 

 

Matrix:

 

 

Problem Matrix Table.pbix

 

Need Help

4 Replies

  • dieterbe_vdb's avatar
    dieterbe_vdb
    Frequent Visitor
    2 years ago

    Thanks for trying out.

    Bizarre.... any idea where the difference comes from?

    It seems that in my pibx file the matrix "remembers" that Customer D didn't had any entry with a low prio in an active status and so when filtering out the Prio field the matrix only takes the entries of customer A in into account resulting in "2" (it should be "3") for the Total All Prio measure. Very strange indeed that your file, using the same measures, does behave as expected.

    So this leaves me in a situation where I can't trust the Matrix visual any longer (unless if we can find a logical explanation for it).

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dieterbe_vdb ,

     

    Thanks for the reply from ThxAlot , please allow me to provide another insight: 

     

    The hierarchical structure of the matrix is closely related to the context of the dax expression and, as a result, often causes some unexpected results. You can use dax expressions to create a dummy table and then perform calculations with the data in the dummy table to ensure the accuracy of the data.

    MEASURE = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALLSELECTED ( 'Data' ),
        [Status],
        "Count", COUNT ( 'Data'[Customer ] )
    )
RETURN
    SUMX ( FILTER ( _SUMMARIZE, [Status] = MAX ( 'Data'[Status] ) ), [Count] )

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

  • dieterbe_vdb's avatar
    dieterbe_vdb
    Frequent Visitor
    2 years ago

    Coming back to the example I've describe in my first post => I've created a Power BI report with two identical matrix visuals => one of them is behaving as I would expect, the other one isn't.

    Link to Power BI report (incuding the DAX queries I'm referring to underneath):  Problem Matrix Table Bis.pbix

    I examined the DAX queries used by both visuals and the strange thing that I notice, is that for one of the matrix visuals, a NONVISUAL function is added to the query. So alhough both matrices are identical, for an unexplainable reason, Power BI added the NONVISUAL function to one of the matrices.


    The query as I expected him (working matrix):

    ************

    DEFINE
        VAR __DS0FilterTable =
            TREATAS({"D",
                "A"}, 'Data'[Customer ])

        VAR __DS0Core =
            SUMMARIZECOLUMNS(
                ROLLUPADDISSUBTOTAL('Data'[Status], "IsGrandTotalRowTotal", 'Data'[Prio], "IsDM1Total"),
                __DS0FilterTable,
                "v__Qty", '_Measures'[# Qty],
                "Total_All_Prio", '_Measures'[Total All Prio]
            )

        VAR __DS0PrimaryWindowed =
            TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Data'[Status], 1, [IsDM1Total], 0, 'Data'[Prio], 1)

    EVALUATE
        __DS0PrimaryWindowed

    ORDER BY
        [IsGrandTotalRowTotal] DESC, 'Data'[Status], [IsDM1Total] DESC, 'Data'[Prio]



    ************

    The query that creates bizarre results:
    ************

    DEFINE
        VAR __DS0FilterTable =
            TREATAS({"D",
                "A"}, 'Data'[Customer ])

        VAR __DM3FilterTable =
            TREATAS({"Active",
                "Passive"}, 'Data'[Status])

        VAR __DS0Core =
            SUMMARIZECOLUMNS(
                ROLLUPADDISSUBTOTAL(
                    'Data'[Status], "IsGrandTotalRowTotal",
                   
                    // Question: why is NONVISUAL appearing here?
                    'Data'[Prio], "IsDM1Total", NONVISUAL(__DM3FilterTable)
                ),
                __DS0FilterTable,
                "v__Qty", '_Measures'[# Qty],
                "Total_All_Prio", '_Measures'[Total All Prio]
            )

        VAR __DS0PrimaryWindowed =
            TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Data'[Status], 1, [IsDM1Total], 0, 'Data'[Prio], 1)

    EVALUATE
        __DS0PrimaryWindowed

    ORDER BY
        [IsGrandTotalRowTotal] DESC, 'Data'[Status], [IsDM1Total] DESC, 'Data'[Prio]


    ************

    So question remains: what causes the NONVISUAL function to be added?

    Many thanks!!!