Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to Solution.
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.
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.
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!
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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
77 | |
60 | |
58 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |