Forum Discussion

superhayan's avatar
superhayan
Helper I
2 years ago
Solved

Making Table Visual that is Dynamic to New Parameter option chosen

Hello all,

 

My raw data is a list of transaction record with date, time, product, $ values etc info. The data will be refreshed daily and I have used the New Parameter function to make a slicer to set the period for the cumulative total calculation of $ values to be 3/6/9/12 months dynamatically as below.

 

 

My question is: I will need to add a table visual into this dashboard page to show all the transaction records that are involved in this period - which I just basically need to show the transaction records without any measures needed. In this way, when I set to 12 months, the table will show all the transaction records that are done during 12/5/2023 - 11/5/2024; and when I choose 6 months, the table will show all transaction records that are done during 12/11/2023 - 11/5/2024. (ddmmyyyy format)

 

I tried to use the new parameter to add a new column in the raw data table to indicate whether each transaction record is "In-Period" such that I can limit the table visual to show only "In-Period" records. However it doesn't work because raw data table can only show static data. Please can I know how can I create a record table visual that is dynamic to the new parameter I choose?

 

Sorry that I cannot share any data file because I cannot access to any cloud drive with my work laptop. Thanks in advance!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi superhayan 

     

    Unfortunately that this is almost unachievable, but maybe you can consider the alternative workaround:

    Here I change the data, two 3.15.2024

    Then change the measure:

    _Sales =
VAR _currentDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        MAX ( 'Table'[Date] ) >= [Start]
            && MAX ( 'Table'[Date] ) <= [End],
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDate )
        )
    )

    Then create a matrix like this:

    You can alse use the conditional formatting to highlight the data wihch have more than one date:

    Color =
VAR _currentDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDate )
        ) > 1,
        "Red"
    )

    The result is as follow:

    Best Regards

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi superhayan 

     

    Here I create a set of sample:

    Then create a new table:

    Table 2 = {3,6,9,12}

    Add measures:

    Start = 
VAR _slicer = MAX('Table 2'[Value])
RETURN DATE(YEAR(TODAY()),MONTH(TODAY())-_slicer,DAY(TODAY()))
    End = TODAY()
    _Sales =
IF (
    MAX ( 'Table'[Date] ) >= [Start]
        && MAX ( 'Table'[Date] ) <= [End],
    SUM ( 'Table'[Sales] )
)

    The result is as follow:

    Here is the pbix for your reference.

     

    Best Regards

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

    • superhayan's avatar
      superhayan
      Helper I
      2 years ago

      Thanks for your reply! Your solution can almost get what I want to acheive. Just that I don't need to calculate the sum of sales in the table. For example, if there are 2 sales on 2024-02-13 with Sales $101 and $20, then in the table visual, when I choose 3 months period, there will be 2 records of 2024-02-13 of $101 and $20 respectively, intead of 1 summarized record of $121.

       

      Could you guide me how I can change the dax to achieve this? Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi superhayan 

         

        Unfortunately that this is almost unachievable, but maybe you can consider the alternative workaround:

        Here I change the data, two 3.15.2024

        Then change the measure:

        _Sales =
VAR _currentDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        MAX ( 'Table'[Date] ) >= [Start]
            && MAX ( 'Table'[Date] ) <= [End],
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDate )
        )
    )

        Then create a matrix like this:

        You can alse use the conditional formatting to highlight the data wihch have more than one date:

        Color =
VAR _currentDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDate )
        ) > 1,
        "Red"
    )

        The result is as follow:

        Best Regards

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