Forum Discussion

superhayan's avatar
superhayan
Icon for Helper I rankHelper 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...
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.

superhayan's avatar
superhayan
Icon for Helper I rankHelper 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.