Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Experts,
Looking for your support on creating a calcuated column to show the products information by apply sertain filters through dax.
When it comes to my requirment, i have a table like below with sample data
i am joining the above table with my date table by date to date. As per user requirement if user selected a date range, based on that we need to show the count of products along with the respective product values should show like below,
If user selected date range as 01-01-2023 to 31-12-2023, then i need to show the details for unsold products for the month on Nov'2023.
When means unsold count of products: 3 and products info should be Bike & Bus.
product count in a card visual and products info should be on table visual
This should be dynamic based on the user selection of date. So pls do the needful on getting this done and share the sample .pbix if you achive this . Thank you so much for your much appreciated support!
Thanks,
Lavan
Solved! Go to Solution.
Hi @lavankumar1989a, thank you for your additional inforamtion!
Please check the udpdated answer:
UnSoldCount =
VAR SelectedStartDate = MIN('DateTable'[Date])
VAR SelectedEndDate = MAX('DateTable'[Date])
VAR AdjustedEndDate = EOMONTH(SelectedEndDate, -1)
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Date] >= SelectedStartDate &&
'Table'[Date] <= AdjustedEndDate &&
'Table'[Status] = "unsold"
))
UnSoldProduct =
VAR SelectedStartDate = MIN('DateTable'[Date])
VAR SelectedEndDate = MAX('DateTable'[Date])
VAR AdjustedEndDate = EOMONTH(SelectedEndDate, -1)
RETURN
CONCATENATEX(
FILTER(
'Table',
'Table'[Date] >= SelectedStartDate &&
'Table'[Date] <= AdjustedEndDate &&
'Table'[Status] = "unsold"
),
'Table'[Product],
", "
)
Result for your reference:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lavankumar1989a ,Thanks for the reply from @Irwan , please allow me to provide another insight:
DateTable = CALENDAR(DATE (2023, 1, 1), DATE (2023, 12, 31))
UnSoldCount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date] IN ALLSELECTED(DateTable[Date]) && 'Table'[Status]="unsold"))
UnsoldProduct =
CONCATENATEX(
FILTER(
'Table',
'Table'[Date] IN ALLSELECTED(DateTable[Date]) && 'Table'[Status] = "unsold"
),
'Table'[Product],
", "
)
As irwan mentioned, the "Auto" product will also be included in your resut,if I misundertand your issue, please feel free to reply:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-yajiewan-msft and @Irwan for you kind reply.
Let me brief my requirement that what i am looking,
I have a 2 tables 1.fact table & 2. date dimension table and relationship established between fact & date dimension tables using date key or date.
i have a slicer as date range starting from 01-01-2021 to 30-06-2024, when user select the data range as like below,
01-01-2023 to 31-12-2023, first we find the month for max date range which means it's dec'2023 month of max(31-12-2023). based on this we need to show product info as max month-1 means Nov'2023 unslod information need to show in table visual.
if user select time range as 20-06-2023 to 30-11-2023, max month would be Nov'2023 and need to show previous month data from max date selected. which means Oct'2023 unsold if any. this should be dynamic in nature.
hope it's clear, please do let me know if any clarifications needed. Thank you so much for your efforts and help!
Hi @lavankumar1989a, thank you for your additional inforamtion!
Please check the udpdated answer:
UnSoldCount =
VAR SelectedStartDate = MIN('DateTable'[Date])
VAR SelectedEndDate = MAX('DateTable'[Date])
VAR AdjustedEndDate = EOMONTH(SelectedEndDate, -1)
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Date] >= SelectedStartDate &&
'Table'[Date] <= AdjustedEndDate &&
'Table'[Status] = "unsold"
))
UnSoldProduct =
VAR SelectedStartDate = MIN('DateTable'[Date])
VAR SelectedEndDate = MAX('DateTable'[Date])
VAR AdjustedEndDate = EOMONTH(SelectedEndDate, -1)
RETURN
CONCATENATEX(
FILTER(
'Table',
'Table'[Date] >= SelectedStartDate &&
'Table'[Date] <= AdjustedEndDate &&
'Table'[Status] = "unsold"
),
'Table'[Product],
", "
)
Result for your reference:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much @v-yajiewan-msft and much appriceated dor your support. DAX that you shared is working as expected.
hello @lavankumar1989a
please check if this accomodate your need.
as far as i know, there are two ways to show your goal.
1. you can use measure
- create a new measure
Unsold = COUNTROWS(FILTER('Table','Table'[Status]="Unsold"))
2. you can use visual filter
- plot you data in table then tick "Unsold" using visual filter for showing only Unsold
Also I find your result is weird, you said theresult should be Bike and Bus, but there is one Auto with Unsold status on 25-12-2023.
Hope this will help you.
Thank you.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
17 | |
16 |
User | Count |
---|---|
28 | |
27 | |
18 | |
14 | |
14 |