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.
Hi All, I use the following formula to return data from another table and save it to another table
BOS Submitted =
VAR m = Mails[Mail]
RETURN
IF(
CALCULATE(
DISTINCTCOUNT(BOSResponses[SubmittedOn]),
FILTER(ALL(Mails),
Mails[Mail] = m
), Filter(BOSResponses, AND(BOSResponses[SubmittedOn]<=DATE(2020,07,31),BOSResponses[SubmittedOn]>=Date(2020,07,01)))
)>=2,1,IF(
CALCULATE(
DISTINCTCOUNT(BOSResponses[SubmittedOn]),
FILTER(ALL(Mails),
Mails[Mail] = m
), Filter(BOSResponses, AND(BOSResponses[SubmittedOn]<=Date(2020,07,31),BOSResponses[SubmittedOn]>=Date(2020,07,01)))
)>=1,0.5,0) )
Kindly note the date values in this DAX formula is manually choosen by the creator of the report, how to make this value choosen by the user who use the report ( like time slicer ) ??
Solved! Go to Solution.
Hi @MostafaGamal ,
Sorry for that dynamic display of calculated columns is not allowed.
We suggest to create a table visual and a measure to show the result.
First you need to create a date table that has no relationship with other tables. You can add it to a slicer.
Then you can create a measure refer this formula.
BOS Submitted =
VAR m =
MAX ( Mails[Mail] )
VAR _min_date =
MIN ( 'Date'[Date] )
VAR _max_date =
MAX ( 'Date'[Date] )
RETURN
IF (
CALCULATE (
DISTINCTCOUNT ( BOSResponses[SubmittedOn] ),
FILTER ( ALL ( Mails ), Mails[Mail] = m ),
FILTER (
BOSResponses,
AND (
BOSResponses[SubmittedOn] <= _max_date,
BOSResponses[SubmittedOn] >= _min_date
)
)
) >= 2,
1,
IF (
CALCULATE (
DISTINCTCOUNT ( BOSResponses[SubmittedOn] ),
FILTER ( ALL ( Mails ), Mails[Mail] = m ),
FILTER (
BOSResponses,
AND (
BOSResponses[SubmittedOn] <= _max_date,
BOSResponses[SubmittedOn] >= _min_date
)
)
) >= 1,
0.5,
0
)
)
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MostafaGamal ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
If you've fixed the issue on your own please kindly share your solution.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-zhenbw-msft i have create a new table called date and use your formula but all the outcome of the formula are " 0 " no data comes from the calculated coulmn
Hi @MostafaGamal ,
Please check that the new Date table you created is not related to other tables.
If it doesn't work, could you please provide a mockup sample based on fake data?
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MostafaGamal ,
Sorry for that dynamic display of calculated columns is not allowed.
We suggest to create a table visual and a measure to show the result.
First you need to create a date table that has no relationship with other tables. You can add it to a slicer.
Then you can create a measure refer this formula.
BOS Submitted =
VAR m =
MAX ( Mails[Mail] )
VAR _min_date =
MIN ( 'Date'[Date] )
VAR _max_date =
MAX ( 'Date'[Date] )
RETURN
IF (
CALCULATE (
DISTINCTCOUNT ( BOSResponses[SubmittedOn] ),
FILTER ( ALL ( Mails ), Mails[Mail] = m ),
FILTER (
BOSResponses,
AND (
BOSResponses[SubmittedOn] <= _max_date,
BOSResponses[SubmittedOn] >= _min_date
)
)
) >= 2,
1,
IF (
CALCULATE (
DISTINCTCOUNT ( BOSResponses[SubmittedOn] ),
FILTER ( ALL ( Mails ), Mails[Mail] = m ),
FILTER (
BOSResponses,
AND (
BOSResponses[SubmittedOn] <= _max_date,
BOSResponses[SubmittedOn] >= _min_date
)
)
) >= 1,
0.5,
0
)
)
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MostafaGamal ,
I think you're looking for a what-if parameter.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if
@mwegener What IF Paremeter does not allow me to create a date parmeter
its only whole of decimal number
You are right, but the concept is quickly transferred. (See attached PBIX)
Proud to be a Super User!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |