Forum Discussion

sd22's avatar
sd22
Frequent Visitor
3 years ago
Solved

Hi All,

I have a column named pp having numeric values 0 till 12. This pp column is acting as a slicer. Now I want to create the dynamic column let's say if I selected 5 from pp slicer then dynamic column should contain values 0,1,2,3,4,5. Basically the dynamic column will contain values from 0 till the selected value .

 

 

Thnaks & Regards

sd22

DAX
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi sd22 

    it hides some complexity,

    1) supposing you have a Table1 like:

     

    2) try to create another table2 with pp2 column, exactly the same value, but do not relate them. 

     

    3) write a measure like:

     

    measure = 
VAR _value2 = SELECTEDVALUE(Table2[pp2])
VAR _value1 = MAX(Table1[pp])
RETURN
IF( _value1<=_value2, 1, 0)

     

     

    4) plot a slicer with table2[PP] and a table visual with table1[PP]. Choose not summarize for the table visual. Feed the measure to the filter pane of the table visual and choose 1. it worked like:

     

     

7 Replies

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

    Hi,

    i create a second table "slicer" with the pp values and use it in the slicer. I create a measure with following code

     

    Measure = 
    VAR _selectvalue = CALCULATE(SELECTEDVALUE(Slicer[pp]))
    VAR _pp = sumx(FILTER('Table','Table'[pp]<=_selectvalue),'Table'[pp])
    RETURN _pp

    To verfiy the result:

     

    Than use the measure as a filter:

    There has to be NO relationship between both tables.

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi sd22 

    it hides some complexity,

    1) supposing you have a Table1 like:

     

    2) try to create another table2 with pp2 column, exactly the same value, but do not relate them. 

     

    3) write a measure like:

     

    measure = 
VAR _value2 = SELECTEDVALUE(Table2[pp2])
VAR _value1 = MAX(Table1[pp])
RETURN
IF( _value1<=_value2, 1, 0)

     

     

    4) plot a slicer with table2[PP] and a table visual with table1[PP]. Choose not summarize for the table visual. Feed the measure to the filter pane of the table visual and choose 1. it worked like:

     

     

  • sd22's avatar
    sd22
    Frequent Visitor
    3 years ago

    Hi FreemanZ / andhiii079845 ,

     

    Thankyou for your swift response.

     

    My actual requirement is like . Here is my data set : 

     

     


    Now I wan to create two table visuals like For the Current Month & Running Total. On top of it I have two slicers FY & Posting Period.

     

    My requirement is If I select 2021 FY and Posting Period 004 then in Current Month Table I should show sales according to FY =2021 && Posting Period = 004.
    For Running Total Table I should see the data where Fy =2021 but Posting Period should show cummulative data of sales from Posting Period = 0+ Posting Period = 1+ Posting Period = 2+ Posting Period = 3+ Posting Period = 4.

    Basically In running Total table I should see the cumulative data based on the slicer selection of posting period. If I have selected Posting Period as 002 then In running total table I should be able to see total sales for fy 2021 where posting period = 0,1,2.

    Thanks & Regards

    sd22

     

    • andhiii079845's avatar
      andhiii079845
      Solution Sage
      3 years ago

      This is a completey new question right? -> perhaps a new topic? 

      • sd22's avatar
        sd22
        Frequent Visitor
        3 years ago
        Hi andhiii079845,

        Yes this can be considered as a new query because I thought creating dynamic calculated column
        will work but as no relation is there . It is not working.
        This can be treated as a new question. Kindly help me with this.

        Thanks & Regards


        Sd22