cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-jiascu-msft Super Contributor
Super Contributor

Re: Change a value by a variable in measure

Hi @frostys,

 

Regarding to syntax error, you missed a ")" before ">TODAY()".

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft Super Contributor
Super Contributor

Re: Change a value by a variable in measure

Hi @frostys,

 

Did you solve it? Can you share the answer or mark the proper answer as a solution please?

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
frostys Regular Visitor
Regular Visitor

Re: Change a value by a variable in measure

Hi @v-jiascu-msft,

 

I didn't find the solution yet

v-jiascu-msft Super Contributor
Super Contributor

Re: Change a value by a variable in measure

Hi @frostys,

 

Isn't that formula a solution even if it can work? Can you share a dummy file? To be honest, your scenario isn't  complicated.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
frostys Regular Visitor
Regular Visitor

Re: Change a value by a variable in measure

Maybe the solution working, but not for me. Could be a different problem. So I can't tell if this is the right formula yet.

v-jiascu-msft Super Contributor
Super Contributor

Re: Change a value by a variable in measure

Hi @frostys,

 

Can you share a dummy sample of your data? Please try the demo in the attachment.

Selected time =
VAR selected =
    SELECTEDVALUE ( 'Time slicer'[Time slicer] )
RETURN
    IF ( ISBLANK ( selected ), 0, selected )

3

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft Super Contributor
Super Contributor

Re: Change a value by a variable in measure

Hi @frostys,

 

The root cause is that we can't use a slicer to change the values of a Calculated table. That's why these solutions here can't work in your scenario. The workaround could be like below. Please give it a try.

1. Create a measure, filter out the blank values.

Measure =
VAR CustomersPurchasedLast3Months =
    SELECTCOLUMNS (
        FILTER (
            'Data input',
            EOMONTH ( 'Data input'[Date], [Selected time] ) > TODAY ()
        ),
        "Customer", [Controlling Customer Name]
    )
RETURN
    IF (
        MIN ( 'Data input'[Controlling Customer Name] ) IN CustomersPurchasedLast3Months,
        BLANK (),
        1
    )

2. The other function that count the numbers of customers also needs to be changed. 

customerAmount =
VAR CustomersPurchasedLast3Months =
    SELECTCOLUMNS (
        FILTER (
            'Data input',
            EOMONTH ( 'Data input'[Date], [Selected time] ) > TODAY ()
        ),
        "Customer", [Controlling Customer Name]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Data input'[Controlling Customer Name] ),
        NOT 'Data input'[Controlling Customer Name] IN CustomersPurchasedLast3Months,
        ALL ( 'Data input' )
    )

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,058 guests
Please welcome our newest community members: