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

Helper V

6 years ago

Solved

How to inset Dynamic Value in calculated column formula

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

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.

8 Replies

vanessafvg
Community Champion
6 years ago
are you looking for this? selectedvalue() to get the value from your slicers? https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/
mwegener
Most Valuable Professional
6 years ago
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
  Helper V
  6 years ago
  mwegener What IF Paremeter does not allow me to create a date parmeter
  its only whole of decimal number
  - mwegener
    Most Valuable Professional
    6 years ago
    Hi MostafaGamal
    
    You are right, but the concept is quickly transferred. (See attached PBIX)
    
    What-IF-Date.pbix62 KB

v-zhenbw-msft

Community Support

6 years ago

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.

v-zhenbw-msft
Community Support
6 years ago
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
  Helper V
  6 years ago
  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
    Community Support
    6 years ago
    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.

Forum Discussion

How to inset Dynamic Value in calculated column formula

8 Replies

Related content

Dynamic Rank Calculation

If formula

Dynamic Variable in Calculated Columns

DAX Formula Help

formula