Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Dynamic variable selections

    Posted Aug 17, 2022 01:37 PM
    Hello,

    In data table I have 3 columns are code, colour and qty, In model table contain colour and no.

     

    There is relationship in-between two tables which is "colour".

     

    I am trying to get the sum of qty of each code depends on the variable colour no of selection by using measure.

     

    I don't want create new calculated column option because I need to create lot of column for each no of selections according to the model.

     

    I don't know how can I do multiple selection in table visuslation against each colour.

     

    Example:

    If I select the L01 and no 4 in visual then wherever matched L01 in data table, sum of data table L01 qty X 4.

     

    The same thing for rest of the colour no of selection. The no of selection keep change and variable according to the colour.

     

    If there is no selection on the visual against the colour then return blanks.

    DATA VAR.pbix
    Dropbox remove preview
    DATA VAR.pbix
    Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!
    View this on Dropbox >

    https://www.dropbox.com/scl/fi/yt5y1o2i4w27kpfbyu69y/Book2.xlsx?dl=0&rlkey=ejcxphnv6kw84mh3pxpw640rs



    ------------------------------
    Ab Ab
    Engineering
    ------------------------------


  • 2.  RE: Dynamic variable selections

    Posted Aug 17, 2022 08:14 PM
      |   view attached
    @Ab Ab,
    I didn't really understand your requirements, but based on some assumptions I made from the sample you provided, I took a guess at what you need.

    I modified that sample .pbix file you provided, and am attaching the new version here (please see page 2 in the file).  Please take a look at it and let us know if this achieves what you intended.​

    Basically, I created a separate table for each of your Colour variables (L01 - L07), and then created a measure to calculate the result.  The code for this measure is as follows:

    Result = 
    VAR currentColor = MAX(DATA[COLOUR])
    VAR colorValue = 
        SWITCH(
            currentColor,
            "L01", MAX(L01[L01 Value]),
            "L02", MAX(L02[L02 Value]),
            "L03", MAX(L03[L03 Value]),
            "L04", MAX(L04[L04 Value]),
            "L05", MAX(L05[L05 Value]),
            "L06", MAX(L05[L05 Value]),
            "L07", MAX(L07[L07 Value]),
            BLANK()
        )
    VAR currentQty = MAX(DATA[QTY])
    RETURN
        colorValue * currentQty​

    Hope that helps.
    Kaz.

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    DATA VAR new.pbix   108 KB 1 version


  • 3.  RE: Dynamic variable selections

    Posted Aug 23, 2022 06:49 PM
    Edited by Ab Ab 27 days ago
      |   view attached

    Hello,

    Thanks for your response. I am new in this forum so I am struggling to use it.

    Yesterday, I reply to you both ways personally and normal response but I am not sure you received normal response even I can't ale to see the my message as well?
    Can you please let me know where can I able to see the send message?

    Thanks for your suggestion regarding my first query. I can follow-up your suggestion.

    Regarding my other query's I need your assistance.

    3. I want total based on the slicer no of selection.

       Example:  28

    4. I want overall sum of qty required (the one you made a measure column in data table "Result" column overall sum of qty) based on the slicer color code no of selections.

    Example: Total slicer selection is 28 across all the color code and overall sum of qty is 4546

    5. Also, I need summary of sum of qty by color code based on the color code no of slicer selections.

    6.  I would like to get the same logic in new calculated column as well. How can get it? I copy the same formula in new calculated column but it giving wrong sum of qty.

    can you please help me to make a same logic in new calculated column

    Power BI file attached.



    ------------------------------
    Ab Ab
    Engineering
    ------------------------------

    Attachment(s)

    pbix
    DATA VAR new.pbix   112 KB 1 version


  • 4.  RE: Dynamic variable selections

    Posted 27 days ago

    Thanks for your reply and sorry for the late response. 

    Thanks for understanding my requirements and yours solution working perfectly. Thank you. 



    ------------------------------
    Ab Ab
    Engineering
    ------------------------------



  • 5.  RE: Dynamic variable selections
    Best Answer

    Posted Aug 27, 2022 04:01 PM
    Edited by Ab Ab 27 days ago
      |   view attached
    @Ab Ab,
    Sorry for the slow response.  I don't think I can answer all of your questions, but I will try to answer some.

    After reading your second message, I think I better understand what you are trying to achieve.  I would suggest making the following changes (I'm attaching a new .pbix file that contains these changes):

    First, create a new measure for each of your input variables, that give the selected value for that variable.  And in this measure, you can determine if more than one item is selected, and in that case it will return a blank - this condition will satisfy the situation where you do not make a selection in your slicer; and this works because when you have not made a selection in the slicer, then all of the values for that input variable are visible to the model, so it thinks that variable has multiple values.  Here is an example of one of these measures:

    L1_Value = 
    VAR itemsSelected =
        COUNTX(L01, L01[L01 Value])
    RETURN
        IF(
            itemsSelected = 1,
            MAX(L01[L01 Value]),
            BLANK()
        )​

    Second, you should modify the measure I suggested previously, called "Result".  The way I structured that measure previously it would only work for each row of the DATA table, but would not work well as an aggregation.  Try this version instead:
    Result = 
        SUMX(
            DATA,
            SWITCH(
                DATA[COLOUR],
                "L01", [L1_Value],
                "L02", [L2_Value],
                "L03", [L3_Value],
                "L04", [L4_Value],
                "L05", [L5_Value],
                "L06", [L6_Value],
                "L07", [L7_Value],
                BLANK()
            ) *
            DATA[QTY]
        )​
    Finally, you can create a matrix visualization, placing the Qty on the rows, Colour on the columns, and Result in the values, like this:

    And the visual would look something like this, when all the "Code" are selected:
    Hopefully that answers most of your questions.

    You had also asked about creating a calculated column with the same formula as the Result measure.  You will not be able to achieve that, because when you have a calculated column, the slicers are not active.  In other words, the values in a calculated column are calculated for each row the table, and this occurs before the table is used in a visualization.  And slicers are only active on a page where there is a visualization, so when the column is being calculated, those slicers do not exist.  And, because the value of the Result measure relies on the values selected in the slicers, it will not be possible to create a calculated column that produces the same values.

    Please let us know if I understood your request, and if this helps get you closer to your desired solution.

    Kaz.

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    DATA VAR new_r2.pbix   111 KB 1 version