How to inset Dynamic Value in calculated column formula

[MostafaGamal]

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 ) ?? 

[v-zhenbw-msft]

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.

[MostafaGamal]

@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 

[v-zhenbw-msft]

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.

[v-zhenbw-msft · Accepted 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.

[mwegener]

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

[MostafaGamal]

@mwegener  What IF Paremeter does not allow me to create a date parmeter 

its only whole of decimal number 

[mwegener]

Hi @MostafaGamal 

You are right, but the concept is quickly transferred. (See attached PBIX)

[vanessafvg]

are you looking for this? selectedvalue() to get the value from your slicers? https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/