Forum Discussion

Varshini1234567's avatar
Varshini1234567
Frequent Visitor
1 year ago
Solved

Sort by measure in a matrix (Field parameter

Hi Team,

 

I have a problem, I am trying to sort based upon the measure the hierarchy in matrix should be sorted in descending order

 

Measure 

plan= var LV = SELECTEDVALUE(RowMatrix[slicer column])
 return
 CALCULATE(
    sum(Raw_data[Sum of Value]),
    Raw_data[plan]=LV,
    Raw_data[Date (Year)]=FORMAT(YEAR(TODAY()),""))
 
I have a matrix which used field paramter as hierarchy in matrix , the field parameter is created as we had a condition to sort data less than 10 to have row named others , so a new column is created named pg budget , pg mtp, pg rbu1,pg rbu2 from sum of value of raw data. slicer column is used field parameter is the slicer used in report

in my raw data the scenario column contains 

Actual

Budget

MTP

RBU1

RBU2

 

Now when im trying to sort the matrix based upon the rankx 

rank = var nonblank = FILTER(
            ALL(Raw_Data),
            [Plan_3pages] <> BLANK() &&
            Raw_Data[Scenario] in {"Budget","MTP","RBU1","RBU2"}
        )
        RETURN
SWITCH(
    TRUE(),

    // Case: Project Name is visible in the matrix
    ISINSCOPE(Raw_Data[Project Name]),
        IF(
            [Plan_3pages]<>BLANK(),
            RANKX(
                nonblank,
                [Plan_3pages],
                ,
                DESC,
                DENSE
            ),
            BLANK()
        ),

    // Budget
    SELECTEDVALUE(RowMatrix[slicer column]) = "Budget",
        IF(ISINSCOPE(Raw_Data[pg Budget])&&
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg Budget]), "Others"),
            999,
            IF(
                [Plan_3pages] <> BLANK(),
                RANKX(
                    ALLSELECTED(Raw_Data[pg Budget]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ) * RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ),
                BLANK()
            )
        ),

    // MTP
    SELECTEDVALUE(RowMatrix[slicer column]) = "MTP",
        IF(ISINSCOPE(Raw_Data[pg MTP])&&
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg MTP]), "Others"),
            999,
            IF(
                [Plan_3pages] <> BLANK(),
                RANKX(
                    ALLSELECTED(Raw_Data[pg MTP]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ) * RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ),
                BLANK()
            )
        ),


    SELECTEDVALUE(RowMatrix[slicer column]) = "RBU1",
        IF(ISINSCOPE(Raw_Data[pg rbu1])&&
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu1]), "Others"),
            999,
            IF(
                [Plan_3pages] <> BLANK(),
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu1]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ) * RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ),
                BLANK()
            )
        ),


    SELECTEDVALUE(RowMatrix[slicer column]) = "RBU2",
        IF(ISINSCOPE(Raw_Data[pg RBU2])&&
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu2]), "Others"),
            999,
            IF(
                [Plan_3pages] <> BLANK(),
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu2]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ) * RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                ),
                BLANK()
            )
        ),

    BLANK()
) the value gets sorted correctly in set area and budget/mtp/rbu1/rbu2 level but if i sort project name then blank value comes 1st , if i add any value like 99 to blank values then actual which is no were usedin slicer also gets filtered, even if i filter excluding actual i don't get it? how to solve the problem? is there any other approach to this
Need Help
Show and Tell
Tips and Tricks
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    Hi Varshini1234567,
    I believe you need to tweak the DAX logic. Could you please use below DAX logic to get non blank projects

    Rank = 
Var LV = SELECTEDVALUE(RowMatrix[slicer column]) 
var nonblank = FILTER(
            ALL(Raw_Data),
            NOT(ISBLANK([Plan_3pages]))&& 
            Raw_Data[Scenario] = LV &&
            Raw_Data[Project Name] <> BLANK()
        )
        RETURN
SWITCH(
    TRUE(),

    // Case: Project Name is visible in the matrix
    ISINSCOPE(Raw_Data[Project Name]),
        IF(
            ISBLANK([Plan_3pages]), BLANK(),
            RANKX(
                nonblank,
                [Plan_3pages],
                ,
                DESC,
                DENSE
            ),
            BLANK()
        ),

    // Budget
    LV = "Budget", && ISINSCOPE(Raw_Data[pg Budget]),
        IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg Budget]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg Budget]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    // MTP
    LV = "MTP" && ISINSCOPE(Raw_Data[pg MTP]),
	IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg MTP]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg MTP]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    LV = "RBU1" && ISINSCOPE(Raw_Data[pg rbu1]),
	IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu1]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu1]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),


    LV = "RBU2" && ISINSCOPE(Raw_Data[pg rbu2]),
        IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu2]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu2]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    BLANK()
)

     

    Thanks,
    If you found this solution helpful, please consider giving it a LikešŸ‘ and marking it as Accepted Solutionāœ”. This helps improve visibility for others who may be encountering/facing same questions/issues.

4 Replies

  • freginier's avatar
    freginier
    Solution Sage
    1 year ago

    Hi, 

     

    You can fix the sorting and blank issue with two key changes:

    1. Exclude 'Actual' rows directly in your measure using:

      Raw_Data[Scenario] IN { "Budget", "MTP", "RBU1", "RBU2" }

    2. Avoid ranking blank values to prevent them from appearing first:

      IF([Plan_3pages] = BLANK(), BLANK(), RANKX(...))

    Also, donā€™t use ALLSELECTED without filtering, as it brings back 'Actual'. Use FILTER(...) inside RANKX to exclude blanks and "Others" cleanly.

     

    freginier

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Hi Varshini1234567,
    I believe you need to tweak the DAX logic. Could you please use below DAX logic to get non blank projects

    Rank = 
Var LV = SELECTEDVALUE(RowMatrix[slicer column]) 
var nonblank = FILTER(
            ALL(Raw_Data),
            NOT(ISBLANK([Plan_3pages]))&& 
            Raw_Data[Scenario] = LV &&
            Raw_Data[Project Name] <> BLANK()
        )
        RETURN
SWITCH(
    TRUE(),

    // Case: Project Name is visible in the matrix
    ISINSCOPE(Raw_Data[Project Name]),
        IF(
            ISBLANK([Plan_3pages]), BLANK(),
            RANKX(
                nonblank,
                [Plan_3pages],
                ,
                DESC,
                DENSE
            ),
            BLANK()
        ),

    // Budget
    LV = "Budget", && ISINSCOPE(Raw_Data[pg Budget]),
        IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg Budget]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg Budget]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    // MTP
    LV = "MTP" && ISINSCOPE(Raw_Data[pg MTP]),
	IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg MTP]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg MTP]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    LV = "RBU1" && ISINSCOPE(Raw_Data[pg rbu1]),
	IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu1]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu1]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),


    LV = "RBU2" && ISINSCOPE(Raw_Data[pg rbu2]),
        IF(
            CONTAINSSTRING(SELECTEDVALUE(Raw_Data[pg rbu2]), "Others"),
            999,
                RANKX(
                    ALLSELECTED(Raw_Data[pg rbu2]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                     ) * 
                    RANKX(
                    ALLSELECTED(Raw_Data[SET Area]),
                    [Plan_3pages],
                    ,
                    DESC,
                    DENSE
                )
        ),

    BLANK()
)

     

    Thanks,
    If you found this solution helpful, please consider giving it a LikešŸ‘ and marking it as Accepted Solutionāœ”. This helps improve visibility for others who may be encountering/facing same questions/issues.

  • v-venuppu's avatar
    v-venuppu
    Community Support
    1 year ago

    Hi Varshini1234567 ,

    Thank you for reaching out to Microsoft Fabric Community.

    Thank you ajaybabuinturi freginier for the prompt response.

    I wanted to check if you had the opportunity to review the information provided and resolve the issue..? If the response has addressed your query, please accept it as a solution, so other members can easily find it.

    Thank you.

  • v-venuppu's avatar
    v-venuppu
    Community Support
    1 year ago

    Hi Varshini1234567 ,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.