Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to show the prior 3-month data count when selecting a date in the slicer. Created date table and related with txn table.
I can get the expected result if pass the slicer selected value as a measure to calc measure. However, I've to disconnect the relationship b/w date and Txn table.
select slicer= apr
measure=selected slicer
dax_calc = calculate(distinctcount(),filter(txn,txndate>=measure-3 && txndate<measure)
since date table is common slicer for multiple charts and relationship with Txn table. Is there possible to get the above logic using dax calc without creating another standalone date table(without relationship) ?
with the relationship, it sticks to each row and unable to get the prior month
Eg.,
slicer - apr
id,dt,st
1,jan,high
2,feb,low
3,mar,low
4,apr,medium
result
high, 1
low, 2
Thank you.
Solved! Go to Solution.
Hi @aj1107,
You can refer to below steps to build a last N month slicer.
1. Add column to convert dt to date.
Date = DATEVALUE([dt]&"/2017")
2. Use above date column to create a slicer table.
Selector Table2 = ADDCOLUMNS(VALUES(Test[Date]),"Month",FORMAT([Date],"mmmm"))
3. Add measure to get selected date.
Select = IF(HASONEVALUE('Selector Table2'[Date]),VALUES('Selector Table2'[Date]),BLANK())
4. Write measure to count id and and filter records.
Count ID = IF(MAX([Date])<[select],COUNT(Test[id]),BLANK())
5. Create visuals.
Regards,
Xiaoxin Sheng
Hi @aj1107,
You can refer to below steps to build a last N month slicer.
1. Add column to convert dt to date.
Date = DATEVALUE([dt]&"/2017")
2. Use above date column to create a slicer table.
Selector Table2 = ADDCOLUMNS(VALUES(Test[Date]),"Month",FORMAT([Date],"mmmm"))
3. Add measure to get selected date.
Select = IF(HASONEVALUE('Selector Table2'[Date]),VALUES('Selector Table2'[Date]),BLANK())
4. Write measure to count id and and filter records.
Count ID = IF(MAX([Date])<[select],COUNT(Test[id]),BLANK())
5. Create visuals.
Regards,
Xiaoxin Sheng
Thanks for sharing the steps.
Quick question, I already have a date dimension related to test table. Per the given steps I need to create another date slicer table without a relationship.
existing date slicer is referred to multiple charts within a page. now i need to have another slicer for the last N month chart.
Is that possible to use the existing date dimension (Related->test) without another slicer table. By selecting any single date evaluate using dax while retaining the relationship.
Hi @aj1107,
I think you can use the current calendar table as the source of slicer, but it will count through all similar month records, so I think you should add a year filter.
Regards,
Xiaoxin Sheng
Hi ,
I have a pretty much similar issue where i need to show last 6 months data based on the selection of the slicer which is basically a month slicer(Jan,Feb) .
In my model i do have one date dimension on which multiple measures has been calculated so i can't have other date dimension
We were able to achieve it by providing a slicer of date which is basically coming from a different table which has been created apart from the date dimensiion.
Can you please guide me through what could be necessary steps to be taken to achieve this scenerio ?
Any help would be really appreciated.
Regards
Prabin Nepak
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |