Forum Discussion

JofrainVisda's avatar
JofrainVisda
Advocate I
1 year ago

Dynamic column Value

I have this column in powerbi, a concatenation of my FY column which is the fiscal year that consists FY24 and FY25, and Period column which consists of P1 to P12. I also created a Period Number Column that extracts the numbers in the Period Column.  So if the period is P12, the period number column on that row is 12.

I want to add another column that indicates Yes or No based on the selected value on the Period slicer in the dashboard. Please someone save me. 

Example: If I have selected P5 in the slicer, then FY25 - P5 down to FY25 - P1 rows will have "Yes" value in that new column.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

7 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi JofrainVisda 

    Can you please try the below DAX.

     

    output =
    VAR SelectedPeriod = MAX('Table'[Period Number]) 
    RETURN
    IF(
    MAX('Table'[Period Number]) <= SelectedPeriod,
    "Yes",
    "No"
    )

     

     

    If you found this post helpful, please accept it as the solution.

  • JofrainVisda's avatar
    JofrainVisda
    Advocate I
    1 year ago

    This is what I've tried and did not work.

    Created a measure:

    Selected Period =
    VAR SelectedVal = SELECTEDVALUE(vw_Tableau2[Period])

    return
    IF(SelectedVal = "P1",1,
    IF(SelectedVal = "P2",2,
    IF(SelectedVal = "P3",3,
    IF(SelectedVal = "P4",4,
    IF(SelectedVal = "P5",5,
    IF(SelectedVal = "P6",6,
    IF(SelectedVal = "P7",7,
    IF(SelectedVal = "P8",8,
    IF(SelectedVal = "P9",9,
    IF(SelectedVal = "P10",10,
    IF(SelectedVal = "P11",11,
    IF(SelectedVal = "P12",12))))))))))))

    Created a column:
    Period Checker =

    IF(vw_Tableau2[Current FY?] = BLANK(),"Yes",
        IF([Selected Period] = 1, "Yes",
        IF([Selected Period] = 2,
            IF(vw_Tableau2[Period Number] <= 2,"Yes"),
        IF([Selected Period] = 3,
            IF(vw_Tableau2[Period Number] <= 3,"Yes"),
        IF([Selected Period] = 4,
            IF(vw_Tableau2[Period Number] <= 4,"Yes"),
        IF([Selected Period] = 5,
            IF(vw_Tableau2[Period Number] <= 5,"Yes"),
        IF([Selected Period] = 6,
            IF(vw_Tableau2[Period Number] <= 6,"Yes"),
        IF([Selected Period] = 7,
            IF(vw_Tableau2[Period Number] <= 7,"Yes"),
        IF([Selected Period] = 8,
            IF(vw_Tableau2[Period Number] <= 8,"Yes"),
        IF([Selected Period] = 9,
            IF(vw_Tableau2[Period Number] <= 9,"Yes"),
        IF([Selected Period] = 10,
            IF(vw_Tableau2[Period Number] <= 10,"Yes"),
        IF([Selected Period] = 11,
            IF(vw_Tableau2[Period Number] <= 11,"Yes"),
        IF([Selected Period] = 12,
            IF(vw_Tableau2[Period Number] <= 12,"Yes","No"))))))))))))))

    Returning result: 
    regardless of the selected period, the column returns YES to all rows. Seems the the measure is not dynamically working in a calculated column
    • Deku's avatar
      Deku
      Super User
      1 year ago

      Calculated columns and tables are generate on semantic model refresh. Slicer selection will have no effect, on these

    • danextian's avatar
      danextian
      Super User
      1 year ago

      Hi JofrainVisda 

      That won't work because calculated columns and tables do not respond to slicer selections. While they can influence what is displayed in the report view, they do not dynamically change within the model. In a calculated column, SELECTEDVALUE will either return blank or the alternate result. To achieve dynamic values that respond to slicer selections, measures must be used within visuals. Additionally, a separate column in the fact Continuous Period table is needed to store the period as a number, along with a disconnected table whose column will be used in a slicer to ensure all periods remain visible. Using a column from a related table restricts the visible rows to only those selected.

       

      Please see the attached sample pbix.

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

        Hi JofrainVisda ,

        Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

        If you have any further questions, feel free to reach out.
        Thank you for being a valued member of the Microsoft Fabric Community Forum!